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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-04-15 : 07:43:40
|
| Thel writes "SQLTeam.comI am a member of a software development team designing a shrink-wrapped product that will utilize SQL Server as the DBMS. The application we are developing will be used to store and retrieve various types of information. The main database table within schema should be flexible enough to allow the storage of data records that have various numbers of fields.For example, say the end-user wants to store available options for a particular type of automobiles, such as power windows, automatic transmission, four-wheel drive, etc… This is no problem to implement for options that are generally provided on today’s automobiles. The issue that concerns us most is scalability. That is the ability to extend the current option list to include new automotive options that are yet to be developed. We have tested the possibility of simply adding an additional 25 columns to the Available_Options table that will allow for the additions of new customer defined options. By adding the additional expansion columns still allows us to fall within established performance requirements and will solve our problem of allowing the customer to add an additional 25 options per automobile type without requiring custom code modifications to the application. The definitions of the newly add option data will be maintained within a Options_Definitions table.My question is. How does SQL Server handle these initially blank or empty columns? Also if data is not contained within a field does SQL Server automatically compress the field to minimize database size? And finally, is there a recommended maximum column count when designing a table? We are considering a max of 200 additional options per automobile type.Any ideas or potential problems you may see will be greatly appreciated.Thanks,Thel Petty" |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-04-15 : 08:07:39
|
| No, no, NO, NO, NOOOOOO!!!Normalization!Please, have someone on your team learn about database design and normalization, or hire a dba that understands these things.Not to be abrupt (well...) but, you approach is wrong. The relational model is not designed to work with repeating groups. You need to normalize you data tier.Jay White{0} |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-15 : 09:08:41
|
quote:
Not to be abrupt
too late.... You need to be concerned with Rows, not columns.For example, if you have a cra, and you "know" the permanent physical properties of a car, then you would list them as columns (null or not null). If you want to list additional features that may or may not exists at the time of the production of that car (and this is even true for the columns you know about), the you might want a CarOptions table. This would have the id of the car, and an option id (you would of course have an options table that list all available options). You would now be table driven, and could add options "on the fly".But I agree with Jay:quote:
Please, have someone on your team learn about database design and normalization
This is very important indeed...it is very difficult to work backwards when a poor model is implemented (did I say very difficult?)Good Luck.PS Make sure you search the articles section of the site. Very hepfulBrett8-) |
|
|
|
|
|
|
|
|
Table Columns