N-Many Relationship
-
Hello All,
Anyone ever implement a many-many-many-many relationship? Or N-Many? Example:
Staff belong to more than one dept, have more than one position in that department, have more than one phone number for each position held.
Another example: Storing X,Y,Z axis and Time values in a normalized format.
Confusing?
-Isaiah -
Maybe something like:
Table Departments
pk_DepID
Table Positions
Pk_PosID
Table Phones
Pk_PhoneID
Table_Pos_Dep_Phone
fk_PosID,fk_DepID,fk_PhoneID, Pk_Association
* Noel -
noeld,
Your example has repeated information. Not quite normalized.
-Isaiah -
can you explain what's repeated ?
* Noel -
This method is also known as the asossiation table. It helps maintain many to many relations. You can split the association table into several but the effect is the same.
you some how need to Associate in the end Person with Department with phone with possition and all you are doing is stablishing that model
BTW I only put the keys of each tables not the rest of the attributes but well I hope you know that.
* Noel -
noeld, good stuff.
I implemented a staff database where a person only had only one internal, external, mobile number but could belong to more than one dept, report to more than one superior (this enabled me to store the company's full hierarchy). So I was interested in your comments.
In your scenario what does Pk_Association do and what would it refer to?
Far away is close at hand in the images of elsewhere.
Anon. -
Repeated data in NoelD's example:
Table_Pos_Dep_Phone
AssocID DepID PosID fk_PhoneID
1D1P1228374 2D1P1232527 3D1P2234222 4D1P2123423 5D1P2123423 6D2P1123423 7D2P1123423 8D2P3123423 9D2P3123423 10D2P3123423
I'd suggest something like:
tblDept: DeptID (PK) Description e.g. 'Finance' ...
tblJob: JobID (PK) Description e.g. 'Clerical Assistant' ...
tblPost: PostID (PK) DeptID (FK) JobID (FK) Description (non-normal data for illustration only) e.g. 'Finance Clerical Asst 3' ...
tblStaff: StaffID (PK) Name e.g. 'Williamson' ...
tblPosition: PositionID (PK) PostID (FK) StaffID (FK) Description (non-normal data for illustration only) e.g. 'Williamson - Finance Clerical Asst 3'
tblPhoneNo: PhoneNoID (PK) PositionID (FK) PhoneNo
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Phone nos should be all different (at least within each group) in example data.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
stax68,
I understand that you can divide the association table further until you get never more than 2 FK per association. In your case it was splitted in two. I was trying to guide the thought process of the poster. And I believe your answer is correct
for maximum normalization of this case.* Noel
* Noel -
David,
I had also being in that boat quite a bit. For example a person can be in more than one department, with more than one position, belong to more than one Facility and have differents phones
Fortunately for me no matter what association was there for the employee he/she will report to just one superior and because of that the self join on the employees table solved the trick for the hierarchy but if you are dealing with things that could have multiple parents there is no other way (AFAIK)but to create association tables.
In my experience some times normalizing too much on lenghty many to many chains may make the queries difficult but that should not be an excuse to try it for the first time, should it get really complicated then denormalization is always there
it just shouldn't be the first option
* Noel -
Thanks for sharing your thoughts on some of the options available for implementing the N-Many concept. This is a topic that deserves further discussion as this situation is encountered in database design but not always implemented correctly. And theirs not much information on the subject.
I'd like to here if any other developers have tackled this concept and if they've discovered any other solutions than those allready posted.
Thanks
-Isaiah -
I believe you should 'fully' normalise your OLTP system and if the queries get too difficult/expensive, develop a reporting platform (data marts etc.,etc.,etc.). It's the best way in the long run. And even in the short run, when we are not yet dead or even much older.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Personally, I think the problem is a tendency among many developers (myself included, when I was young and foolish) to see fewer tables as more efficient. There's no reason not to have a dozen different relationship tables, if that's what you need to model your data. Trying to "streamline" them into a single table is likely to just bring you headaches.
In this case, is there a relationship between "Position" and "Phone?" If yes, then define it, in business terms first. Once you do that, the basic table structure should be fairly clear. It will likely be a cascading relationship. So, you'll create a PersonDepartment table, then a PersDeptPosition table that references that, then a PersDeptPosPhone table that references that. Depending on your exact needs, you may want to denormalize that structure, back to noeld's original example. But, recognize that any denormalization is a trade-off, and may cause issues down the road.
If no, then don't try and force them into the same table. Create a PersonDepartment table, a PersonPosition table, and a PersonPhone table.
One of the real problems with getting fancy with the table design, is that you are then forced to get fancy with your queries and application. Stick with a traditional table design, and most of your basic queries will virtually write themselves.
-
Normalisation improves OLTP performance, ensures consistency and exposes the true nature of your data model's subject matter.
Many-to many tables shouldn't just be rgarded as link or join or association tables - a quirk of the relational system. They usually represent real entities (facts, to transplant some DW terminology) with their own properties of interest. For example, being in a position has a start and end time. When someone is using a phone number for a given job, we might want to know if it's a company mobile, personal landline etc. For a random example: how many out-of-hours-support numbers used by past and present IT Support staff since X became IT Manager have been their own personal mobiles?
Given that phone use or job-doing are activites we want to record, we should avoid shoe-horning the data into a schema which doesn't even admit that they exist.
Embrace normalisation (in databases only). Do your joins. Make views and table functions. Do what you have to do. Deliver rich, flexible consistent data to your reporting system and let the big queries go on there. Are you with me brothers and sisters?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
i am with you brother. My current inherited db puts data in generic tables. For example a 'persons' table contains customers, agents, account mangers, sales men all in recursive hierarchrys of n depth; a total nightmare.
proper normalised ER modeling still needs to be popularised...
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply