Threading
asked on
Multiple relationships between two tables.
Hi Experts!
My apology for question not being clear, as any perfectly formulated question by itself shows an answer.
Im designing database in SQL Server. Not taking into consideration all other Entities, I have two. One entity is "Document" another "Operator".
Document:
DocID
OrderReceivedDate
OrderReceivedByOperator--- ------>
ProductName
ProductReceivedDate
ProductReceivedByOperator- ------>
etc.
I have three relationships from Document table to Operator Table. Each stage on document can be filled by different Operators/People.
question:
1) Is it ever done and considered "Good Practice", elegant?
2) what are better alternatives?
3) would design matter if I had two relationships, or ten relationships?
Regards.
My apology for question not being clear, as any perfectly formulated question by itself shows an answer.
Im designing database in SQL Server. Not taking into consideration all other Entities, I have two. One entity is "Document" another "Operator".
Document:
DocID
OrderReceivedDate
OrderReceivedByOperator---
ProductName
ProductReceivedDate
ProductReceivedByOperator-
etc.
I have three relationships from Document table to Operator Table. Each stage on document can be filled by different Operators/People.
question:
1) Is it ever done and considered "Good Practice", elegant?
2) what are better alternatives?
3) would design matter if I had two relationships, or ten relationships?
Regards.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you kelvinsparks for quick reply.
It looks like there is information on multiple relationships between two tables, in MS Access. Would solution differ in MS Access to SQL Server.
Maybe SQL Server has some "Best Practice" methodology that is more elegant?
It looks like there is information on multiple relationships between two tables, in MS Access. Would solution differ in MS Access to SQL Server.
Maybe SQL Server has some "Best Practice" methodology that is more elegant?
ASKER
Ok, Could you please confirm that my understanding is right.
1) If Scalability is not an issue e.g. if I know that I will not have additional attributes or extra relationships, then Implement multiple relationships.
2) If there are ten relationships then it is recommended to have linking table.
1) If Scalability is not an issue e.g. if I know that I will not have additional attributes or extra relationships, then Implement multiple relationships.
2) If there are ten relationships then it is recommended to have linking table.
MS Access has less functionality than SQL Server, but you still follow the same database design practises. As both myself and kelvinsparks have indicated your proposed solution is fine.
What make you think there is a more elegant solution?
What make you think there is a more elegant solution?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The other consideration is the number of queries one has to generate and on which tables.
relationship tables prevent eliminate the need of having a table with relationship references that exceed the data points.
The issue also deals with the types of relationships could require a separate relationship table.
relationship tables prevent eliminate the need of having a table with relationship references that exceed the data points.
The issue also deals with the types of relationships could require a separate relationship table.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Olaf_Doschke said "enables to determine the state of a recipe without joins". In my situation it seems that this is what I will be using this table for.
Document has parts which are done by different Operators, main use of this Entity is seeing how much progress is done.
Attribute of who(Operator) the task was performed by is just bureaucratic necessity.
Thank you Experts, that answers that question.
PS: Is there a book/resource where most difficult ERD problems are collected and possible/recommended solutions are given?
Document has parts which are done by different Operators, main use of this Entity is seeing how much progress is done.
Attribute of who(Operator) the task was performed by is just bureaucratic necessity.
Thank you Experts, that answers that question.
PS: Is there a book/resource where most difficult ERD problems are collected and possible/recommended solutions are given?
Google database design patterns e.g. http://stackoverflow.com/questions/145689/relational-database-design-patterns
- DocumentId
- OperationId
- Relationship (Id or Text)