Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1775
  • Last Modified:

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.
0
Threading
Asked:
Threading
4 Solutions
 
Kelvin SparksCommented:
What you describe is quite accepatble/normal. I frequently find that setup - it is OK.


Kelvin
0
 
Dale BurrellDirectorCommented:
As Kelvinsparks says its perfectly acceptable to do that. However its not very scalable, so for example if you are looking at a few relationships then I'd consider a linking table e.g.

- DocumentId
- OperationId
- Relationship (Id or Text)
0
 
ThreadingAuthor Commented:
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?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
ThreadingAuthor Commented:
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.
0
 
Dale BurrellDirectorCommented:
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?
0
 
Dale BurrellDirectorCommented:
There is no right answer as to whether to have multiple direct relationships or having a linking table. They are both solutions, whether one is better will depend on the detail of your situation. And if you decide later you prefer the other way you can always change it then.
0
 
Eric FlammSenior ConsultantCommented:
I would also point out that scalability refers not to the width of the table (number of relationships) but to the depth (how many rows are stored). As the number of rows increases, the resources required to insert and update the table will increase due to index maintenance. By storing only a foreign key to a linking table, you can reduce the size of the indexes and therefore the time required to update them. If you don't expect the table to grow very big, then your proposed design should work fine, as the previous posters indicated.
0
 
arnoldCommented:
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.
0
 
Olaf DoschkeSoftware DeveloperCommented:
From a real world eample I can also assure you the design is very ok. I do maintain a database where recipes are processed in always the same steps, 1. entering, 2. transferring data to a lab pc (with electronic scale attached) 3. weighing in and 4. transferring the recipe back to the central backend. I also don't log this processing state into a seperate table, but have 4 userid fields for each state.

Is it unelegant? Why? Others have looked at it from the performance aspect. I wouldn't say that matters much with a few fields, the question is, will there likely be further steps in the process, which matter? In my case a next step would be users judging the final "meals" (in fact this is not about food). This is really done, but in a seperate part of the database.

Keeping the users within the same record seemed natural to me, these steps are always done and always logged and it enables to determine the state of a recipe without joins. The only query taking more effort was the one finding recipes a user has processed in any way, as that needed to put every such field in the where clause.

I would not say the number of such relationsship matters, what will matter is what queries you will want to run against the data (eg query documents an operator was involved in any way) and how fixed the number of users or operators is, who are involved.

Bye, Olaf.
0
 
ThreadingAuthor Commented:
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?
0
 
Dale BurrellDirectorCommented:
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now