Solved

Multiple relationships between two tables.

Posted on 2012-03-25
11
1,569 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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 78

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

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

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

Suggested Solutions

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Viewers will learn how the fundamental information of how to create a table.

752 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