SQLTeam.com | Weblogs | Forums

Design


#1

I am creating the new database and I have requirement to created 50 fields.
I want to design the tables/table which should be faster to access the data should be in seconds..

Please suggest the best options and pros and cons..

Option 1:Create a single denormalize table with 50 fields since it has one unqueID which is unique for the record.

Option 2:Normalize the tables and create more tables and relationship..

Thanks for your help in advance !


#2

It is hard to give a generic answer, but in general, if your database is read/write, I would tend towards normalizing the tables, at least for the most part.


#3

I would avoid creating a table that had multiple columns where those columns could be rows in a child-table. So columns with names like "Month-1", "Month-2", ... "Month-N" would usually be better in a child table.


#4

Thanks for immediate response..

I forgot to mention 40 fields are Boolean type and other fields are of integer and varchar,datetime.

The database tables should perform the read/write/delete.

Based on the above scenario can you suggest options..


#5

Same applies: If they have a "Month-1", "Month-2" type meaning they should be in a child table, if they are completely different entities then they can be separate columns in a single table.

If the Boolean are different properties for the object that's fine. If they are an array of basically the same property - so for some rows you will use Col1, Col2, ... Col5 [to store the 5 available values] and for other rows you will use Col1, Col2 [i.e. you only have two values to store on that row] then you should be using a child table. (it seems to me that that is unlikely/impossible for Boolean datatype ... but ...)

Your definition is very vague, so the answers you get may be WAY off mark.


#6

First, normalize through at least 3NF. Then, review the design, and if you have to for practical reasons, denormalize. That's the standard design process, and it should work just fine for you here.