Solved

Multiple relationships between two tables.

Posted on 2012-03-25
11
1,484 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
Comment Utility
What you describe is quite accepatble/normal. I frequently find that setup - it is OK.


Kelvin
0
 
LVL 21

Expert Comment

by:Dale Burrell
Comment Utility
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
Comment Utility
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
 

Author Comment

by:Threading
Comment Utility
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
Comment Utility
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 21

Accepted Solution

by:
Dale Burrell earned 125 total points
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:arnold
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now