Single wide table or multiple narrow tables

Question

Single wide table or multiple narrow tables

touchmeknot

SSCommitted

Points: 1769
More actions
June 8, 2011 at 1:18 pm

#251471

Hello,
I am new to database design so pardon me, if the question sounds silly.
I have about 25 fields (all of them are either tinyint or bit). I am unable to decide if I should use a single wide table or multiple narrow tables.
We are using SQL 2005.
I will appreciate feedback/comments or any link for guidance.
Thank you.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

You have not provided enough information to help guide you. Are all 25 fields related to a single entity? If you have several tables are you going to have a bunch of 1:1 records?

CEWII

touchmeknot SSCommitted Points: 1769 More actions · Answer 2

Ok. Yes, all these are related to single entity. The relationship is going to be 1:1. It doesn't matter if it is a single table of multiple tables, as in, all the fields should exist for single enitity ( highly unlikely that they won't, can't rule it out though), and there will be one record in each of the table(s).

I am thinking that there will additional join to all the 4 tables if I decide on multiple tabe and is it worth or not ?

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

Given what you describe I would go single table. Even if you had 25 int columns your effective row size would be about 210 bytes and you could get about 38 rows/page. you said there was a mix of int and bit columns, if you put your bit columns together in the definition the DB engine will usually condense them into a single byte (if you have 8 or less), or more bytes if you have more than 8. What this means is VERY dense storage of those fiels and you could get between 38-80 rows/page. And at 100K rows the table would be about 21MB. Very workable.

The only reason I would consider another table is if you had a few fields that were HEAVILY updated and all the rest were VERY static, and even then only break the most heavily used columns off. but I am rarely a fan of this because now I have to deal with two tables and what happens if the data does not exist in the secondary table?

Short answer, go with one.

CEWII

touchmeknot SSCommitted Points: 1769 More actions · Answer 4

I appreciate your quick response along with reasons. Thank you very much!

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

Elliott Whitlow

SSC Guru

Points: 102402

June 8, 2011 at 2:18 pm

#1335947

You are very welcome..

CEWII

aaron.reese SSChampion Points: 13498 More actions · Answer 6

you could go one stage further and have one table with only one value column.

Create table myTable

(

SourceRecordID,

ValueType,

Value

)

you would then store (up to) 25 entries in the table for each source record.

This has the following two advantages:

You do not need to modify the table design and any business processes that link to it when a new valueType is added.

You are not storing sparse data: if a source record does not have a particular value, you simply do not enter it in the table.

however....

The datatype of the value must always be the same, which effectively means an nvarchar as it will convert to (almost) every other data type

The data is harder to get out because you may have to join to the table many times to get each valueType

The volatility of the data (adding, modifying and deleteing records) may cause unneccesary re-paging of the indexes.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

While I agree that this method would work my own experience shows that anytime you need to query more than a single value it starts to get complicated. And while multiple references to the same table aren't particularly painful every table added to the query costs.

I would be very careful with this methodology. I have used it periodically, typically for configuration data where I wanted to be able to add configuration information and not be required to change the table. Data that would typcially be used in a join was not stored there as I never wanted to be required to do multi-joins against that table. The values stored there were typically id values pointing elsewhere or a flag indicating enabled or a value indicating how far in a set processing had gotten. I get the values with a UDF and those UDFs are not used in the SELECT portion of the statement, sometimes I use that function to get that value and pass it to the query with a variable, sometimes in the WHERE clause directly..

My basic position, be careful.

CEWII