Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
imadiamond2016
Starting Member
29 Posts |
 Posted - 2012-03-20 : 14:16:06
|
| I have to load data into my database. The kind of data I have to import is explained below:For a particular row, either field PartID would be NULL OR field GroupID will be NULL, and the other available columns refers to the NON-NULL entity. I have following three options:To use one database table, which will have one unified column say ID, which will have PartID and GroupID data. But, in this case I won't be able to apply foreign key constraint, as this column will be containing both entities' data.To use one database table, which will have columns for both PartID and GroupID, which will contain the respective data. For each row, one of them will be NULL, But in this case I will be able to apply foreign key constraint.To use two database tables, which will have similar structure, the only difference will be the column PartID and GroupID. In this case I will be able to apply foreign key constraint.One thing to note here is that, the table(s) will be used in import processes to import about 30000 rows in one go and will also be heavily used in data retrieve operations. Also, the other columns will be used as pivot columns.Can someone please suggest what should be best approach to achieve this? |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2012-03-20 : 20:06:33
|
| Of course it depends on what the table represents but from what you've written, I'd go with having both columns with a nullable FK. You can also place a CHECK constraint to makes sure COALESCE(PartID, GroupID) is not NULL for extra integrity.If you ever did need the 2 tables (and I can't think why) you could build 2 views from that existing table.If you're doing a parts hiearachy type of thing a better way might be to keep the parent item on the child and not make the distinction between a part and a group, but I'm totally guessing at your requirement here. |
 |
|
|
imadiamond2016
Starting Member
29 Posts |
Posted - 2012-03-21 : 01:38:55
|
| Thanks for providing the solution. I have decided to take both columns in one table. |
|
|
|
|
|
|
|
|
SQL Server 2008 - Database Design Query