DB Column Advice

[mickeyj2]

Hi,
I'm in the process of designing my database model and I had a couple of questions:

- RE: Number of columns. One of my main tables currently has 85 columns. This table identifies many intricate features of my product and cannot really be adequately broken out into individual tables. I understand that there's no official count on suggested number of columns, but just thought I'd field other professionals.

- RE: Column Contents. This is the trickier one. I so far have about 15 tables. The way I've designed these tables is to first access my main table, and then perform a series of JOINS to the supporting tables to ultimately acquire all of the data about my product. However, one of my tables is for "OperatingSystem", which is populated with various OS' like Windows, Linux, etc. Each OS is identified with a PK. In my main table, I want to say that "these particular operating systems are supported", therefore, a column which is a list of the operating system IDs. For example, MainTable.OperatingSystem column is a nvarchar(30) with the contents of "1,2,3,4".
My reasoning here is that I can read this list into my application and acquire my supporting OS then.
Is that logic efficient? I have a couple of other columns that are lists of Primary Key IDs. When possible, I'll use JOINS, but these are more convenient.

Thanks in advance for your advice.

mickeyj2

 

[ptheriault]

You will find that table with 85 Columns to be to wide. I would split it in two.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Crowley16]

in your 85 column table, can any of the columns be null? if so, how many?

if your table is well normalised, then the column count isn't that important, unless you start to hit 8k rows... However it is very strange to have a normalised system which contains 85 columns in 1 table, but only you can make that judgement.

as for your 2nd point, seems like you have a many to many relationship and should be using a junction table. It's not really a very good idea to combine primary keys into strings...

--------------------
Procrastinate Now!

 

[mrdenny]

I'm going to be lazy today and simply dito what Crowley16 said.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mickeyj2]

Hi, Thank you for all of your responses, but let me pose the following scenario.

Suppose I just want to store the "available options" for a product. Is it not a good practice to store the Primary Keys for these options as a list in a table column?

For example, suppose I have a table called CARS:and it is defined as:
CarID (int 4)
TireOptions (nvarchar 20) - tells me which tires widths this car can support.

I only want one entry for this particular Car type, so I store my data as follows:

CARS:
Carid TireOptions
1 "1,2,3,4"

Now, suppose I have another table called TIRES:and it is defined as:
TireID (int 4)
TireDesc (nvarchar 20) - tells me the tire widths

And I've stored my data as follows:

TIRES:
Tireid TireDesc
1 17-in.
2 18-in.
3 19-in.
4 20-in.

The way I process this information in my application is if I'm showing my user a car and I say "This car can support these tires", I simply read in CARS.TireOptions, then for each option, I grab the appropriate information from TIRES.

You see, this car isn't actually built yet, so I want to tell my users what's available.

If this isn't the best method, then what other options do I have?

Thanks for your help on this. I'm building my database, but I want to do it right.

mickeyj2

 

[mrdenny]

There are two different techniques which people will generaly use.

1. A many to many relationship table between the two. Basically you create a table with two columns carid and tireid. The table has one record for each tire that the car can have.

2. Use a bitwise field. This requires changing the id values of your tire table. The values would now look like this.
1 17-in.
2 18-in.
4 19-in.
8 20-in.
16
32
64
128
256
512
1024
...

You then sum up all the id's that are available for the car and store that value in the car table. Then to find out if a specific tire can be used you use the bitwise operators to see if the value you are looking for is in the field.

The first option is easier to setup and work with, however it does require more space. If you are going to be searching the list for all the cars that can use 17" tires then go with option 1. If you are simply going to be storing the values then option 2 may be the better option.

You can read up more on Bitwise in Books OnLine.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com