 | |
#1
 
| |||
| |||
|
Multiple tables refer to one -To use foreign keys or not? -
05-29-2008
, 04:30 PM
#2
| |||
| |||
|
|
As one of my first encounters with SQL (The RDBMS used is MySQL 5.1), my colleague and I have designed the following (it's a part of a catalog system for a museum): We have an "attachments" table, which can refer to "works_of_art", "people", "reproductions" (Let's say, more than three tables). So a work of art can have attachments, a person can have a different attachment, etc. To be able to relate each object to its eventual attachments, one solution I imagined is to put N foreign keys from each of the tables referred in attachments (if my use of the verb refer is incorrect, please do point it out). Let's say that, for now, there are no fields exclusive to any of the referred tables, so creating different attachment tables is not the best obvious choice. The alternative solution we came up with was to use two fields in "attachments" to identify the relations: one with the id of the referred table, and one with the "type". So 'works of art' would be 0, 'people' would be 1... The reasoning behind the decision was that my colleague felt that too many fields would remain empty with the first solution, and that adding a new type of "attachee" was simpler/more efficient than adding a new foreign key. This solution doesn't strike me as the best. We want to use cakephp to build the app that will use the database, and while this solution would eventually work with some manual labour, I feel it grates against it when the rest of the design does not. Since my knowledge of SQL is tenuous at best, I wanted to listen to some informed opinions about this before arguing again. By all means, if this is a WTF, point it out, the reason, and possibly an alternative. Saludos, Adriano |
re-inventing EAV, yet again?
#3
| |||
| |||
|
|
As one of my first encounters with SQL (The RDBMS used is MySQL 5.1), my colleague and I have designed the following (it's a part of a catalog system for a museum): We have an "attachments" table, which can refer to "works_of_art", "people", "reproductions" (Let's say, more than three tables). So a work of art can have attachments, a person can have a different attachment, etc. To be able to relate each object to its eventual attachments, one solution I imagined is to put N foreign keys from each of the tables referred in attachments (if my use of the verb refer is incorrect, please do point it out). Let's say that, for now, there are no fields exclusive to any of the referred tables, so creating different attachment tables is not the best obvious choice. The alternative solution we came up with was to use two fields in "attachments" to identify the relations: one with the id of the referred table, and one with the "type". So 'works of art' would be 0, 'people' would be 1... The reasoning behind the decision was that my colleague felt that too many fields would remain empty with the first solution, and that adding a new type of "attachee" was simpler/more efficient than adding a new foreign key. This solution doesn't strike me as the best. We want to use cakephp to build the app that will use the database, and while this solution would eventually work with some manual labour, I feel it grates against it when the rest of the design does not. Since my knowledge of SQL is tenuous at best, I wanted to listen to some informed opinions about this before arguing again. By all means, if this is a WTF, point it out, the reason, and possibly an alternative. Saludos, Adriano |
re-inventing EAV, yet again?
#4
| |||
| |||
|
|
As one of my first encounters with SQL (The RDBMS used is MySQL 5.1), my colleague and I have designed the following (it's a part of a catalog system for a museum): We have an "attachments" table, which can refer to "works_of_art", "people", "reproductions" (Let's say, more than three tables). So a work of art can have attachments, a person can have a different attachment, etc. To be able to relate each object to its eventual attachments, one solution I imagined is to put N foreign keys from each of the tables referred in attachments (if my use of the verb refer is incorrect, please do point it out). Let's say that, for now, there are no fields exclusive to any of the referred tables, so creating different attachment tables is not the best obvious choice. The alternative solution we came up with was to use two fields in "attachments" to identify the relations: one with the id of the referred table, and one with the "type". So 'works of art' would be 0, 'people' would be 1... The reasoning behind the decision was that my colleague felt that too many fields would remain empty with the first solution, and that adding a new type of "attachee" was simpler/more efficient than adding a new foreign key. This solution doesn't strike me as the best. We want to use cakephp to build the app that will use the database, and while this solution would eventually work with some manual labour, I feel it grates against it when the rest of the design does not. Since my knowledge of SQL is tenuous at best, I wanted to listen to some informed opinions about this before arguing again. By all means, if this is a WTF, point it out, the reason, and possibly an alternative. Saludos, Adriano |
re-inventing EAV, yet again?
#5
| |||
| |||
|
|
As one of my first encounters with SQL (The RDBMS used is MySQL 5.1), my colleague and I have designed the following (it's a part of a catalog system for a museum): We have an "attachments" table, which can refer to "works_of_art", "people", "reproductions" (Let's say, more than three tables). So a work of art can have attachments, a person can have a different attachment, etc. To be able to relate each object to its eventual attachments, one solution I imagined is to put N foreign keys from each of the tables referred in attachments (if my use of the verb refer is incorrect, please do point it out). Let's say that, for now, there are no fields exclusive to any of the referred tables, so creating different attachment tables is not the best obvious choice. The alternative solution we came up with was to use two fields in "attachments" to identify the relations: one with the id of the referred table, and one with the "type". So 'works of art' would be 0, 'people' would be 1... The reasoning behind the decision was that my colleague felt that too many fields would remain empty with the first solution, and that adding a new type of "attachee" was simpler/more efficient than adding a new foreign key. This solution doesn't strike me as the best. We want to use cakephp to build the app that will use the database, and while this solution would eventually work with some manual labour, I feel it grates against it when the rest of the design does not. Since my knowledge of SQL is tenuous at best, I wanted to listen to some informed opinions about this before arguing again. By all means, if this is a WTF, point it out, the reason, and possibly an alternative. Saludos, Adriano |
re-inventing EAV, yet again?
#6
| |||
| |||
|
|
As one of my first encounters with SQL (The RDBMS used is MySQL 5.1), my colleague and I have designed the following (it's a part of a catalog system for a museum): We have an "attachments" table, which can refer to "works_of_art", "people", "reproductions" (Let's say, more than three tables). So a work of art can have attachments, a person can have a different attachment, etc. To be able to relate each object to its eventual attachments, one solution I imagined is to put N foreign keys from each of the tables referred in attachments (if my use of the verb refer is incorrect, please do point it out). Let's say that, for now, there are no fields exclusive to any of the referred tables, so creating different attachment tables is not the best obvious choice. The alternative solution we came up with was to use two fields in "attachments" to identify the relations: one with the id of the referred table, and one with the "type". So 'works of art' would be 0, 'people' would be 1... The reasoning behind the decision was that my colleague felt that too many fields would remain empty with the first solution, and that adding a new type of "attachee" was simpler/more efficient than adding a new foreign key. This solution doesn't strike me as the best. We want to use cakephp to build the app that will use the database, and while this solution would eventually work with some manual labour, I feel it grates against it when the rest of the design does not. Since my knowledge of SQL is tenuous at best, I wanted to listen to some informed opinions about this before arguing again. By all means, if this is a WTF, point it out, the reason, and possibly an alternative. Saludos, Adriano |
re-inventing EAV, yet again?
#7
| |||
| |||
|
|
As one of my first encounters with SQL (The RDBMS used is MySQL 5.1), my colleague and I have designed the following (it's a part of a catalog system for a museum): We have an "attachments" table, which can refer to "works_of_art", "people", "reproductions" (Let's say, more than three tables). So a work of art can have attachments, a person can have a different attachment, etc. To be able to relate each object to its eventual attachments, one solution I imagined is to put N foreign keys from each of the tables referred in attachments (if my use of the verb refer is incorrect, please do point it out). Let's say that, for now, there are no fields exclusive to any of the referred tables, so creating different attachment tables is not the best obvious choice. The alternative solution we came up with was to use two fields in "attachments" to identify the relations: one with the id of the referred table, and one with the "type". So 'works of art' would be 0, 'people' would be 1... The reasoning behind the decision was that my colleague felt that too many fields would remain empty with the first solution, and that adding a new type of "attachee" was simpler/more efficient than adding a new foreign key. This solution doesn't strike me as the best. We want to use cakephp to build the app that will use the database, and while this solution would eventually work with some manual labour, I feel it grates against it when the rest of the design does not. Since my knowledge of SQL is tenuous at best, I wanted to listen to some informed opinions about this before arguing again. By all means, if this is a WTF, point it out, the reason, and possibly an alternative. Saludos, Adriano |
re-inventing EAV, yet again?
#8
| |||
| |||
|
|
As one of my first encounters with SQL (The RDBMS used is MySQL 5.1), my colleague and I have designed the following (it's a part of a catalog system for a museum): We have an "attachments" table, which can refer to "works_of_art", "people", "reproductions" (Let's say, more than three tables). So a work of art can have attachments, a person can have a different attachment, etc. To be able to relate each object to its eventual attachments, one solution I imagined is to put N foreign keys from each of the tables referred in attachments (if my use of the verb refer is incorrect, please do point it out). Let's say that, for now, there are no fields exclusive to any of the referred tables, so creating different attachment tables is not the best obvious choice. The alternative solution we came up with was to use two fields in "attachments" to identify the relations: one with the id of the referred table, and one with the "type". So 'works of art' would be 0, 'people' would be 1... The reasoning behind the decision was that my colleague felt that too many fields would remain empty with the first solution, and that adding a new type of "attachee" was simpler/more efficient than adding a new foreign key. This solution doesn't strike me as the best. We want to use cakephp to build the app that will use the database, and while this solution would eventually work with some manual labour, I feel it grates against it when the rest of the design does not. Since my knowledge of SQL is tenuous at best, I wanted to listen to some informed opinions about this before arguing again. By all means, if this is a WTF, point it out, the reason, and possibly an alternative. Saludos, Adriano |
re-inventing EAV, yet again?
#9
| |||
| |||
|
|
As one of my first encounters with SQL (The RDBMS used is MySQL 5.1), my colleague and I have designed the following (it's a part of a catalog system for a museum): We have an "attachments" table, which can refer to "works_of_art", "people", "reproductions" (Let's say, more than three tables). So a work of art can have attachments, a person can have a different attachment, etc. To be able to relate each object to its eventual attachments, one solution I imagined is to put N foreign keys from each of the tables referred in attachments (if my use of the verb refer is incorrect, please do point it out). Let's say that, for now, there are no fields exclusive to any of the referred tables, so creating different attachment tables is not the best obvious choice. The alternative solution we came up with was to use two fields in "attachments" to identify the relations: one with the id of the referred table, and one with the "type". So 'works of art' would be 0, 'people' would be 1... The reasoning behind the decision was that my colleague felt that too many fields would remain empty with the first solution, and that adding a new type of "attachee" was simpler/more efficient than adding a new foreign key. This solution doesn't strike me as the best. We want to use cakephp to build the app that will use the database, and while this solution would eventually work with some manual labour, I feel it grates against it when the rest of the design does not. Since my knowledge of SQL is tenuous at best, I wanted to listen to some informed opinions about this before arguing again. By all means, if this is a WTF, point it out, the reason, and possibly an alternative. Saludos, Adriano |
re-inventing EAV, yet again?
#10
| |||
| |||
|
|
As one of my first encounters with SQL (The RDBMS used is MySQL 5.1), my colleague and I have designed the following (it's a part of a catalog system for a museum): We have an "attachments" table, which can refer to "works_of_art", "people", "reproductions" (Let's say, more than three tables). So a work of art can have attachments, a person can have a different attachment, etc. To be able to relate each object to its eventual attachments, one solution I imagined is to put N foreign keys from each of the tables referred in attachments (if my use of the verb refer is incorrect, please do point it out). Let's say that, for now, there are no fields exclusive to any of the referred tables, so creating different attachment tables is not the best obvious choice. The alternative solution we came up with was to use two fields in "attachments" to identify the relations: one with the id of the referred table, and one with the "type". So 'works of art' would be 0, 'people' would be 1... The reasoning behind the decision was that my colleague felt that too many fields would remain empty with the first solution, and that adding a new type of "attachee" was simpler/more efficient than adding a new foreign key. This solution doesn't strike me as the best. We want to use cakephp to build the app that will use the database, and while this solution would eventually work with some manual labour, I feel it grates against it when the rest of the design does not. Since my knowledge of SQL is tenuous at best, I wanted to listen to some informed opinions about this before arguing again. By all means, if this is a WTF, point it out, the reason, and possibly an alternative. Saludos, Adriano |
re-inventing EAV, yet again?
 |
« 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.