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 |
|
manojkumarfbd
Starting Member
4 Posts |
 Posted - 2009-04-01 : 11:33:34
|
| HiWe have table with 250 columns having different data types. Now, some more columns need to add for the new requirement. and some more columns may required to add in futrure.Which one will be more efficient structure for the table1. Adding more columns in the same table, which may not be filled all the columns for a records many of them can be NULL.And it may fail updation and insertion if all the columns are filled because of row size constraint. 2. A table with one row per field that has a unique key, a record ID, a field ID, and a field value. If a record has 250 fields filled in, the table would have 250 rows, each with one field value.And field values will be different in data types (varchar,int,float, etc..) therefore different columns may required to hold the filed values. Because one column cannot hold all kind of values. And this structure required more joins to get the result.Please advise better table design solution!!! Any suggestions & comments will be appreciated ThanksManoj Kumar |
|
|
zzzbla
Starting Member
13 Posts |
Posted - 2009-04-03 : 01:44:22
|
| Hi Manoj,You didn't specify the requirements or the SQL Server version you're planning to use.In SQL Server 2005 (and above, no edition restrictions) you may use a single XML column to store the data. The downside is that it'll require more disk space, and it may be slightly harder to query (XML indexes may help with query performance). The advantage is that you won't have to change the schema every time you wish to add an attribute.In SQL Server 2008 (enterprise edition only) you can use a new feature called sparse columns, which is optimized for many columns (up to 30,000 per table) with many NULL values. The downside is that it requires enterprise edition (it's not cheap) and that you have to modify the table schema whenever you wish to add a column.The other option you mentioned (recordID, fieldID, fieldValue - also referred to as EAV - entity, attribute, value) is also an option. Downside is mixing datatypes, issues with indexing and also visualizing the results. If you go this way, it's better to use a varbinary column for the value column.Regards,S. Neumann |
 |
|
|
gvphubli
Yak Posting Veteran
54 Posts |
Posted - 2009-04-03 : 12:44:21
|
| Manoj, your first solution will hit limitation one or the other day. Your second idea is somewhat acceptable, but still the whole design looks odd to me (may be because I don't clear visibility into your requirements).Also, consider the factors like is it a OLTP or OLAP database before you finalize the design.TechnologyYogihttp://gvphubli.blogspot.com/ |
|
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 2009-04-14 : 09:14:14
|
| The second design is bad for performance.It is normal for a table to have a lot of columns but 250?? Are you sure you can't separate the "data logic" over several tables?I wouldn't store it as a XML blob for performance reasons. Think of the DB as nothing more than a data store, the application end should be doing the processing. |
|
|
|
SQLinTheCloud
Starting Member
3 Posts |
Posted - 2009-04-16 : 01:48:10
|
| In order to do a good job with database and table design you MUST understand the data. It doesn't sound like that's the case here. Use the concept of normalization...I'm going to point you to the first article I wrote for the SQL Server Magazine, "SQL by Design: Why You Need Database Normalization", InstantDoc #4887, March 1999. If you have a subscription to the magazine you can link right to it (http://www.sqlmag.com/Article/ArticleID/4887/SQL_by_Design_Why_You_Need_Database_Normalization.html), otherwise you can buy a month's admission to the entire library of content for $6US. What you'll find in the columns that I've been writing for 10 years is a wealth of information on database design, both for transactional databases and warehouses. Good luck...and by the way, neither of your solutions is a good one. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2009-04-16 : 09:11:53
|
| I agree with the above.1 is better but sounds like you might need to normalise.2 sucks - don't go near it.3 XML is the resort of people who don't understand their data |
|
|
|
manojkumarfbd
Starting Member
4 Posts |
Posted - 2009-05-07 : 02:03:51
|
| Thank you all for valuable suggestions. |
|
|
|
|
|
|
|
|
Table Design Issue