Solved

Multiple relationships between two tables.

Posted on 2012-03-25
11
1,548 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
Technology Partners: 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!

 

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

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!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

756 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