 | |
Using a seperate table to 'join' make a table relation
comp.databases.theory comp.databases.theory
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Using a seperate table to 'join' make a table relation -
01-10-2008
, 04:10 AM
#2
| |||||
| |||||
|
|
Hi, In the past I have always made the relationship between two tables using foriegn keys. So, to link employee to employer I would have a column in employee table called employer_id so that one employee could have one employer and one employer may have many employees. Pretty textbook stuff really but what if one employee had two jobs (employers)?? For this I may create the following tables: employee employer_employer_join employer The table employer_employer_join would have two columns that would make the join, employer_id and employee_id, and I would have no need for employer_id in the employee table. This way I could create many relations between the two and not fixed to single relationships for either. I adopted this technique in my last project and it worked for any join I had to make but it was more work and more tables to manage. Is this a recommeded method of implementing joins? Would I only use this method when there are multiple rows on either side (many employees for one employer but also many employers for one employee) |
or if some
Quote:
|
tables use it, it makes more sense to make all joins in a project this way to keep the design theory consistant? |
rules state 1:many, then the data model should reflect 1:many.
Should I carefully
Quote:
|
deptermine the tables that dont require this and just implement foriegn keys in those tables (eg. A car only even has at any one time one colour)? |
And finally, does this technique have a name that I can
Quote:
|
learn more about?? |
Quote:
|
Hopefully the above is clear, Im newish to building large database designs but I understand the importance of getting this part correct from the start. Any help would be much appreciated. Thanks Burnsy |
doing so.
#3
| |||||
| |||||
|
|
Hi, In the past I have always made the relationship between two tables using foriegn keys. So, to link employee to employer I would have a column in employee table called employer_id so that one employee could have one employer and one employer may have many employees. Pretty textbook stuff really but what if one employee had two jobs (employers)?? For this I may create the following tables: employee employer_employer_join employer The table employer_employer_join would have two columns that would make the join, employer_id and employee_id, and I would have no need for employer_id in the employee table. This way I could create many relations between the two and not fixed to single relationships for either. I adopted this technique in my last project and it worked for any join I had to make but it was more work and more tables to manage. Is this a recommeded method of implementing joins? Would I only use this method when there are multiple rows on either side (many employees for one employer but also many employers for one employee) |
or if some
Quote:
|
tables use it, it makes more sense to make all joins in a project this way to keep the design theory consistant? |
rules state 1:many, then the data model should reflect 1:many.
Should I carefully
Quote:
|
deptermine the tables that dont require this and just implement foriegn keys in those tables (eg. A car only even has at any one time one colour)? |
And finally, does this technique have a name that I can
Quote:
|
learn more about?? |
Quote:
|
Hopefully the above is clear, Im newish to building large database designs but I understand the importance of getting this part correct from the start. Any help would be much appreciated. Thanks Burnsy |
doing so.
#4
| |||||
| |||||
|
|
Hi, In the past I have always made the relationship between two tables using foriegn keys. So, to link employee to employer I would have a column in employee table called employer_id so that one employee could have one employer and one employer may have many employees. Pretty textbook stuff really but what if one employee had two jobs (employers)?? For this I may create the following tables: employee employer_employer_join employer The table employer_employer_join would have two columns that would make the join, employer_id and employee_id, and I would have no need for employer_id in the employee table. This way I could create many relations between the two and not fixed to single relationships for either. I adopted this technique in my last project and it worked for any join I had to make but it was more work and more tables to manage. Is this a recommeded method of implementing joins? Would I only use this method when there are multiple rows on either side (many employees for one employer but also many employers for one employee) |
or if some
Quote:
|
tables use it, it makes more sense to make all joins in a project this way to keep the design theory consistant? |
rules state 1:many, then the data model should reflect 1:many.
Should I carefully
Quote:
|
deptermine the tables that dont require this and just implement foriegn keys in those tables (eg. A car only even has at any one time one colour)? |
And finally, does this technique have a name that I can
Quote:
|
learn more about?? |
Quote:
|
Hopefully the above is clear, Im newish to building large database designs but I understand the importance of getting this part correct from the start. Any help would be much appreciated. Thanks Burnsy |
doing so.
#5
| |||||
| |||||
|
|
Hi, In the past I have always made the relationship between two tables using foriegn keys. So, to link employee to employer I would have a column in employee table called employer_id so that one employee could have one employer and one employer may have many employees. Pretty textbook stuff really but what if one employee had two jobs (employers)?? For this I may create the following tables: employee employer_employer_join employer The table employer_employer_join would have two columns that would make the join, employer_id and employee_id, and I would have no need for employer_id in the employee table. This way I could create many relations between the two and not fixed to single relationships for either. I adopted this technique in my last project and it worked for any join I had to make but it was more work and more tables to manage. Is this a recommeded method of implementing joins? Would I only use this method when there are multiple rows on either side (many employees for one employer but also many employers for one employee) |
or if some
Quote:
|
tables use it, it makes more sense to make all joins in a project this way to keep the design theory consistant? |
rules state 1:many, then the data model should reflect 1:many.
Should I carefully
Quote:
|
deptermine the tables that dont require this and just implement foriegn keys in those tables (eg. A car only even has at any one time one colour)? |
And finally, does this technique have a name that I can
Quote:
|
learn more about?? |
Quote:
|
Hopefully the above is clear, Im newish to building large database designs but I understand the importance of getting this part correct from the start. Any help would be much appreciated. Thanks Burnsy |
doing so.
#6
| |||||
| |||||
|
|
Hi, In the past I have always made the relationship between two tables using foriegn keys. So, to link employee to employer I would have a column in employee table called employer_id so that one employee could have one employer and one employer may have many employees. Pretty textbook stuff really but what if one employee had two jobs (employers)?? For this I may create the following tables: employee employer_employer_join employer The table employer_employer_join would have two columns that would make the join, employer_id and employee_id, and I would have no need for employer_id in the employee table. This way I could create many relations between the two and not fixed to single relationships for either. I adopted this technique in my last project and it worked for any join I had to make but it was more work and more tables to manage. Is this a recommeded method of implementing joins? Would I only use this method when there are multiple rows on either side (many employees for one employer but also many employers for one employee) |
or if some
Quote:
|
tables use it, it makes more sense to make all joins in a project this way to keep the design theory consistant? |
rules state 1:many, then the data model should reflect 1:many.
Should I carefully
Quote:
|
deptermine the tables that dont require this and just implement foriegn keys in those tables (eg. A car only even has at any one time one colour)? |
And finally, does this technique have a name that I can
Quote:
|
learn more about?? |
Quote:
|
Hopefully the above is clear, Im newish to building large database designs but I understand the importance of getting this part correct from the start. Any help would be much appreciated. Thanks Burnsy |
doing so.
#7
| |||||
| |||||
|
|
Hi, In the past I have always made the relationship between two tables using foriegn keys. So, to link employee to employer I would have a column in employee table called employer_id so that one employee could have one employer and one employer may have many employees. Pretty textbook stuff really but what if one employee had two jobs (employers)?? For this I may create the following tables: employee employer_employer_join employer The table employer_employer_join would have two columns that would make the join, employer_id and employee_id, and I would have no need for employer_id in the employee table. This way I could create many relations between the two and not fixed to single relationships for either. I adopted this technique in my last project and it worked for any join I had to make but it was more work and more tables to manage. Is this a recommeded method of implementing joins? Would I only use this method when there are multiple rows on either side (many employees for one employer but also many employers for one employee) |
or if some
Quote:
|
tables use it, it makes more sense to make all joins in a project this way to keep the design theory consistant? |
rules state 1:many, then the data model should reflect 1:many.
Should I carefully
Quote:
|
deptermine the tables that dont require this and just implement foriegn keys in those tables (eg. A car only even has at any one time one colour)? |
And finally, does this technique have a name that I can
Quote:
|
learn more about?? |
Quote:
|
Hopefully the above is clear, Im newish to building large database designs but I understand the importance of getting this part correct from the start. Any help would be much appreciated. Thanks Burnsy |
doing so.
#8
| |||||
| |||||
|
|
Hi, In the past I have always made the relationship between two tables using foriegn keys. So, to link employee to employer I would have a column in employee table called employer_id so that one employee could have one employer and one employer may have many employees. Pretty textbook stuff really but what if one employee had two jobs (employers)?? For this I may create the following tables: employee employer_employer_join employer The table employer_employer_join would have two columns that would make the join, employer_id and employee_id, and I would have no need for employer_id in the employee table. This way I could create many relations between the two and not fixed to single relationships for either. I adopted this technique in my last project and it worked for any join I had to make but it was more work and more tables to manage. Is this a recommeded method of implementing joins? Would I only use this method when there are multiple rows on either side (many employees for one employer but also many employers for one employee) |
or if some
Quote:
|
tables use it, it makes more sense to make all joins in a project this way to keep the design theory consistant? |
rules state 1:many, then the data model should reflect 1:many.
Should I carefully
Quote:
|
deptermine the tables that dont require this and just implement foriegn keys in those tables (eg. A car only even has at any one time one colour)? |
And finally, does this technique have a name that I can
Quote:
|
learn more about?? |
Quote:
|
Hopefully the above is clear, Im newish to building large database designs but I understand the importance of getting this part correct from the start. Any help would be much appreciated. Thanks Burnsy |
doing so.
#9
| |||||
| |||||
|
|
Hi, In the past I have always made the relationship between two tables using foriegn keys. So, to link employee to employer I would have a column in employee table called employer_id so that one employee could have one employer and one employer may have many employees. Pretty textbook stuff really but what if one employee had two jobs (employers)?? For this I may create the following tables: employee employer_employer_join employer The table employer_employer_join would have two columns that would make the join, employer_id and employee_id, and I would have no need for employer_id in the employee table. This way I could create many relations between the two and not fixed to single relationships for either. I adopted this technique in my last project and it worked for any join I had to make but it was more work and more tables to manage. Is this a recommeded method of implementing joins? Would I only use this method when there are multiple rows on either side (many employees for one employer but also many employers for one employee) |
or if some
Quote:
|
tables use it, it makes more sense to make all joins in a project this way to keep the design theory consistant? |
rules state 1:many, then the data model should reflect 1:many.
Should I carefully
Quote:
|
deptermine the tables that dont require this and just implement foriegn keys in those tables (eg. A car only even has at any one time one colour)? |
And finally, does this technique have a name that I can
Quote:
|
learn more about?? |
Quote:
|
Hopefully the above is clear, Im newish to building large database designs but I understand the importance of getting this part correct from the start. Any help would be much appreciated. Thanks Burnsy |
doing so.
#10
| |||
| |||
|
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.