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 |
|
jimzjunk
Starting Member
2 Posts |
 Posted - 2005-06-28 : 12:06:47
|
| I would like a few design opinions.I am converting an acccess table to SQL. The original tables has over 150 columns. I have broken alot of it down and the root table has around 10 joins and some text fields which are almost 100% unique from row to row.I need to also store 42 bit fields that are all options (and really will only be used in final reports).From my view point I have three options:1. Leave the 42 fields in the root table2. Create a table to pull out the 42 fields (1 to 1) and join only when they are needed3. I can make a table with the 1764 combonation possibilities (many to 1)3 seems like a bad idea to me because I have to account for all combonations so if I have to add a new row I need to have a script on hand that will add the new combos.Is there any benifit to pulling them out?Thanks |
|
|
Crito
Starting Member
40 Posts |
Posted - 2005-06-28 : 12:28:01
|
| *usually* a table with 150 columns is a good indication that the design isn't normalized. I'm surprised you need to use 10 joins too.What's the primary key on the base table? And what are the foreign keys? If any of the 42 bit attributes apply only to certain foreign keys, then they problably belong in the child tables. If you have a compaound key on the base table and some of the 42 bit attributes only depend on a portion of the primary key, you probably need to split the base table into two, with perhaps a third table in middle for many-to-many relationship.Anyway, just some random guesses based on the very limited info you provided.----------------------------------Gun for hire, have horse, will travel. |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-06-28 : 17:59:20
|
| If all the bit columns are truly attributes of the base table, you should leave them there. SQL Server stores 8 bit columns per byte, so that would only use 6 bytes per row.CODO ERGO SUM |
|
|
|
jimzjunk
Starting Member
2 Posts |
Posted - 2005-06-29 : 09:43:17
|
| Thank you both for the tips.They are attributes for the primary record (needs to be stored for each primary key) so I think I will just keep it there. |
|
|
|
|
|
|
design question