General Database Design Question

[Timinator81]

This is kind of a general question where there is really no clear answer. Just looking for opinions... Here goes.

I have a complaint table with lots of fields. Certain fields are only used with a certain type of complaint. Would it make sense to seperate the large complaint table into 3 sub-tables that hold the fields unique to that type of complaint? This would mean that I would have to use a query to combine the data.

Any opinions that you may have will be helpful.

thanks.

tim

[skhanal]

It's a typical "It Depends" scenario.

If the table is going to have hundreds of thousands (or millions) of rows and you will be running big queries joining this table with four or five other tables, then It's better to keep one table.

If the no of rows are not going to be that big, then also one table is a good idea.

I would suggest three tables if the number of rows is in millions and you do not have big queries requiring all three tables plus few other tables.

[sjimmo]

Under the standard rules of normalization you would seperate this into different tables. I would normally set it up so that the joins were done on a clustered index, in order to increase speed and efficincy of the database queries. (The exact field is hard to determine without knowing the schema).

In this manner, you are reducing the amount of diskspace required for your database. You would only have entries in teh child tables that are required.

[gbarnett]

If your database provides a partitioning solution then great, you get to have one big table but also partition by the 3 unique types of complaint.

Or....just create three seperate tables and cluster them (if your db provides this option).

Anyways, there are always loads of ideas and options. Academia would say normalise all tables in design and modelling and then de-normalise for performance.

If you're not sure what this means, do some google searches for the terms and learn about them.

I tend to design with performance issues in mind, but after years of the tedious way above.

HTH,

Graeme