Please help me with junction tables. I havea large database that makes reporting a NIGHTMARE. The first step is to normalize this db and define relationships. I have hdr, print and line tables. I need to establish a relationship between the three. There is only one print per line but some lines have two hdrs. There are currently date fields in the hdr and print tables but none in the line table. Most of the reports being produced are based on the date. Should I add the date field to the line table and FK for the hdr and print tables to be able to report on both parts of the line in the same query? Currently I have to do a union query with a left and right join to get a report on both parts of the line. I know there must be an easier way if I just set up the relationships differently. Just as background, the other parts are cust, wo, shift, and emps. Then there are what I call line events, dt, co, and ca. There are many wo, emps, and shifts per line. There are many cust who can have many wo on many lines. I have multiple many to many relationships and know I need at least one junct tble, maybe more. Any assistance is greatly appreciated.
Junction Tables
- Thread starter lmg0115
- Start date
wazz
Super Moderator
- Local time
- Tomorrow, 00:20
- Joined
- Jun 29, 2004
- Messages
- 1,711
you need a junction table for every many to many relationship. i can't tell if you know what a junction table is or you just need to sort them out.
it's important to look at the relationship from both sides:
- one line may have only one print | one print may have only one line (?)
- one line may have one or more hdrs | one hdr may have one or many lines (?)
etc.
for someone who doesn't do what you do it's difficult to know what short-forms mean. it would be extremely useful to everyone that works with you on this if you could expand the short-forms into more meaningful names whenever possible. it doesn't always involve too many extra letters.
- print - printer?
- hdrs - harddrive?
- cust, wo, shift, and emps, line events, dt, co, and ca, etc. i can guess what most of these are but i could be wrong.
if you can post an image of the relationships (or tables) it would also be helpful. start with shorter question(s) that deal with what you think are the main tables and work outward.
