Kevin Campbell
New member
- Local time
- Today, 03:33
- Joined
- Feb 23, 2005
- Messages
- 5
I'm having design questions for complicated business logic. I have 4 tables, Author, Plan, Analysis, and Report. 1 or many authors can write 0, 1 or many
Plans, Analysis or Reports. And a Plan, Analysis and Report (all 3) can all have a 1 or many authors associated with it. So thats the first many-to-many. To make things worst, a Plan, Analysis, or Report can have 0, 1, or many Plan, Analysis, or Reports associated with it. In other words, a Plan be associated with a 0, 1 or many Analysis or Reports. An Analysis can be associated with 0, 1 or many Plans or Reports. A Report can be associated with 0, 1 or many Plans or Analysis. So I have 4 entities with all many-manys between them.
So by following traditional rules I would have 4 tables with 6 linking tables (author to plan, author to analysis, author to report, plan to analysis, analysis to report, plan to report). Is there a way to combine multiple linking tables into one or two linking tables shared between the 4 tables. Say one linking table between from author to plan, analysis and report and another linking table between plan, analysis and report? Any clarification would be greatly appreciated. If I had schema view like in access that would give me so much help. Thanks in advance!
Plans, Analysis or Reports. And a Plan, Analysis and Report (all 3) can all have a 1 or many authors associated with it. So thats the first many-to-many. To make things worst, a Plan, Analysis, or Report can have 0, 1, or many Plan, Analysis, or Reports associated with it. In other words, a Plan be associated with a 0, 1 or many Analysis or Reports. An Analysis can be associated with 0, 1 or many Plans or Reports. A Report can be associated with 0, 1 or many Plans or Analysis. So I have 4 entities with all many-manys between them.
So by following traditional rules I would have 4 tables with 6 linking tables (author to plan, author to analysis, author to report, plan to analysis, analysis to report, plan to report). Is there a way to combine multiple linking tables into one or two linking tables shared between the 4 tables. Say one linking table between from author to plan, analysis and report and another linking table between plan, analysis and report? Any clarification would be greatly appreciated. If I had schema view like in access that would give me so much help. Thanks in advance!
