Solved

Multiple relationships between two tables.

Posted on 2012-03-25
11
1,515 Views
Last Modified: 2012-06-27
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
Comment
Question by:Threading
11 Comments
 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 125 total points
ID: 37762454
What you describe is quite accepatble/normal. I frequently find that setup - it is OK.


Kelvin
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 37762459
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
 

Author Comment

by:Threading
ID: 37762462
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:Threading
ID: 37762466
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
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 37762468
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
 
LVL 21

Accepted Solution

by:
Dale Burrell earned 125 total points
ID: 37762470
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
 
LVL 8

Assisted Solution

by:Eric Flamm
Eric Flamm earned 125 total points
ID: 37762802
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
 
LVL 77

Expert Comment

by:arnold
ID: 37763222
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
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 125 total points
ID: 37764557
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
 

Author Closing Comment

by:Threading
ID: 37764782
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
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 37764823
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question