Re: Many to Many Table Linking Architecture
Posted by: Rick James
Date: October 20, 2013 03:57PM

Before discussion arbitrary linkages, let's discuss the following...

A one-to-one linkage is rarely needed -- the two tables should (usually) be just one table.

A many-to-one linkage is easily implemented by having the id of the 'one' as a column in the 'many'. (A 'class' would have 'professor_id' column.) In some cases, the linkage is optional -- then the id column would be NULLable.

A many-to-many linkage is implemented via an extra table:
CREATE TABLE StudentsClasses (
student_id ...,
class_id ...
PRIMARY KEY(student_id, class_id),
INDEX(class_id) );
Perhaps this is like your "dedicated table that links parts with drawings"?
And "jobs to quality issues"? Note: A job with no quality issues would not need any rows in the mapping table.

Note that the three flavors (1:1, 1:N, M:N) are implemented in different ways.

I would suggest these 3 linkage models are the 'right' way to go, even though it may be tedious to initially build the M:N mapping tables. I think you will find out that you don't need to connect as many tables as you fear. And many links are the simpler 1:N variety.

You could search online to see if anyone is offering a sample schema for MRP.

Options: ReplyQuote


Subject
Written By
Posted
Re: Many to Many Table Linking Architecture
October 20, 2013 03:57PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.