SQL Table Design
-
Hi guys (and girls) I have the folowing situation, a Person table, a Company table and a Contract table. I can make contracts with both persons and companies alike so in the Contract table must be a referece to either a person or a company. In this case there are only two tables that need to link to the Contract table, but lets suppose there can be any number. My initial approach is to create another table let's say Entity that has a column named EntityID that is the PK. In the Person table there is a PersonID column that is a PK for the Person table and a FK from the EntityID in the Entity table. The same applies to the Company table having a CompanyID column that is the PK for the Company table and a FK from the EntityID in the Entity table. The ID columns are GUIDs. I use the EntityID as a FK in the Contract table to link either Person or Company to a contract in a SupplierID and a ClientID column. While this design works, there is a small problem: acidentally deleting either a person or a company can leave an orphaned EntityID with no corespondece to the Person or Company table wich will leave the contract without one of its parts. Is there other database design appropriate for this situation that overcomes this issue using referential integrity? -- modified at 18:18 Wednesday 18th January, 2006 Just came up with another ideea, but it involves creating in the Entity table a new column for each referenced table (in this case Person and Company). In this case PersonID and CompanyID columns will be added to the Entity table and they will be FK from the Person and the Company table, for each Entity row only one of them having a value. After that, the row's EntityID will be used in the Contract table. This works but new columns have to be created for each referenced table. Is there any other solution?
-
Hi guys (and girls) I have the folowing situation, a Person table, a Company table and a Contract table. I can make contracts with both persons and companies alike so in the Contract table must be a referece to either a person or a company. In this case there are only two tables that need to link to the Contract table, but lets suppose there can be any number. My initial approach is to create another table let's say Entity that has a column named EntityID that is the PK. In the Person table there is a PersonID column that is a PK for the Person table and a FK from the EntityID in the Entity table. The same applies to the Company table having a CompanyID column that is the PK for the Company table and a FK from the EntityID in the Entity table. The ID columns are GUIDs. I use the EntityID as a FK in the Contract table to link either Person or Company to a contract in a SupplierID and a ClientID column. While this design works, there is a small problem: acidentally deleting either a person or a company can leave an orphaned EntityID with no corespondece to the Person or Company table wich will leave the contract without one of its parts. Is there other database design appropriate for this situation that overcomes this issue using referential integrity? -- modified at 18:18 Wednesday 18th January, 2006 Just came up with another ideea, but it involves creating in the Entity table a new column for each referenced table (in this case Person and Company). In this case PersonID and CompanyID columns will be added to the Entity table and they will be FK from the Person and the Company table, for each Entity row only one of them having a value. After that, the row's EntityID will be used in the Contract table. This works but new columns have to be created for each referenced table. Is there any other solution?
You have a couple of options. One is to use triggers on the Person and Company tables to do brute force referential integrity on the EntityID table. Another is to take your second idea, and just roll it into the Contract table itself. That is, create an FK field for Person, and an FK field for Company, in the Contract table. Yes, this is poor normalization. However, you aren't gaining anything by splitting this out into an Entity table, and you're just increasing the complexity. Another is to create a pair of intermediary tables. Create a ContractPerson table, that handles contracts to people, and a ContractCompany table, that handles contracts to companies. You would need to add some logic, probably through triggers, to make sure that you don't have the same ContractID in both tables. But, probably your best bet is actually to drop back a step further. What exactly is the practical difference between Persons and Companies? You may want to significantly expand your Entity table to actually be the core of the system. It would include any information that is common to any customer, such as address. You would add a field that indicates if the Entity is a Person, Company, or whatever other categories you might have. You would then make child tables that handle data unique to the type of Entity.
-
You have a couple of options. One is to use triggers on the Person and Company tables to do brute force referential integrity on the EntityID table. Another is to take your second idea, and just roll it into the Contract table itself. That is, create an FK field for Person, and an FK field for Company, in the Contract table. Yes, this is poor normalization. However, you aren't gaining anything by splitting this out into an Entity table, and you're just increasing the complexity. Another is to create a pair of intermediary tables. Create a ContractPerson table, that handles contracts to people, and a ContractCompany table, that handles contracts to companies. You would need to add some logic, probably through triggers, to make sure that you don't have the same ContractID in both tables. But, probably your best bet is actually to drop back a step further. What exactly is the practical difference between Persons and Companies? You may want to significantly expand your Entity table to actually be the core of the system. It would include any information that is common to any customer, such as address. You would add a field that indicates if the Entity is a Person, Company, or whatever other categories you might have. You would then make child tables that handle data unique to the type of Entity.
Another is to take your second idea, and just roll it into the Contract table itself. That is, create an FK field for Person, and an FK field for Company, in the Contract table. Yes, this is poor normalization. However, you aren't gaining anything by splitting this out into an Entity table, and you're just increasing the complexity. Well, it is not just the Contracts tabel involved, there are also invoices and all kinds of tabels that store documents and other data that can be applyed to either a persion or a company. And in this case there are only two root tabels but one can imagine a situation that has any number. Adding columns for every one in all the related tabels is not a viable option. The Entity tabel offers a level of abstraction and restricts modifications to a single tabel. Another is to create a pair of intermediary tables. Create a ContractPerson table, that handles contracts to people, and a ContractCompany table, that handles contracts to companies. You would need to add some logic, probably through triggers, to make sure that you don't have the same ContractID in both tables. Again, creating intermediary tabels for every posible case is not a viable option. But, probably your best bet is actually to drop back a step further. What exactly is the practical difference between Persons and Companies? You may want to significantly expand your Entity table to actually be the core of the system. It would include any information that is common to any customer, such as address. You would add a field that indicates if the Entity is a Person, Company, or whatever other categories you might have. You would then make child tables that handle data unique to the type of Entity. Yes, this is the ideea. Entity is the core of the sistem. It links for example the Address table with the specific Person or Company, as a person or a Company can have multiple addresses. So far the best solution is the one I came up the second time, addind a new column in the Entity table for each new table. And not only that, the EntityID equals the value of the column that has the value set(in this case PersonID or CompanyID). That is important because on the data layer I can create a Dataset that has a relation between let's say Person and Address. One is to use triggers on the Person and Company tables to do brute force referential integrity on the EntityID table. I want to restrict as much as possible the use of triggers to enforce referential integr
-
Another is to take your second idea, and just roll it into the Contract table itself. That is, create an FK field for Person, and an FK field for Company, in the Contract table. Yes, this is poor normalization. However, you aren't gaining anything by splitting this out into an Entity table, and you're just increasing the complexity. Well, it is not just the Contracts tabel involved, there are also invoices and all kinds of tabels that store documents and other data that can be applyed to either a persion or a company. And in this case there are only two root tabels but one can imagine a situation that has any number. Adding columns for every one in all the related tabels is not a viable option. The Entity tabel offers a level of abstraction and restricts modifications to a single tabel. Another is to create a pair of intermediary tables. Create a ContractPerson table, that handles contracts to people, and a ContractCompany table, that handles contracts to companies. You would need to add some logic, probably through triggers, to make sure that you don't have the same ContractID in both tables. Again, creating intermediary tabels for every posible case is not a viable option. But, probably your best bet is actually to drop back a step further. What exactly is the practical difference between Persons and Companies? You may want to significantly expand your Entity table to actually be the core of the system. It would include any information that is common to any customer, such as address. You would add a field that indicates if the Entity is a Person, Company, or whatever other categories you might have. You would then make child tables that handle data unique to the type of Entity. Yes, this is the ideea. Entity is the core of the sistem. It links for example the Address table with the specific Person or Company, as a person or a Company can have multiple addresses. So far the best solution is the one I came up the second time, addind a new column in the Entity table for each new table. And not only that, the EntityID equals the value of the column that has the value set(in this case PersonID or CompanyID). That is important because on the data layer I can create a Dataset that has a relation between let's say Person and Address. One is to use triggers on the Person and Company tables to do brute force referential integrity on the EntityID table. I want to restrict as much as possible the use of triggers to enforce referential integr
I think you're making the problem a little more complicated than it needs to be. Check out one-to-one relationships. That should enable you to enforce referential integrity between the tables. The only thing you'd need to add logic to enforce is having a record in the Person table if and only if the EntityType is "Person." And similarly for each of the other entity types. Even that isn't entirely necessary, if you'd rather put logic into your views and stored procedures to make different joins based on the EntityType (a little tricky, but hardly impossible).