April 27, 2004 at 8:00 am
Hi Gurus,
I want to know the advantage of keeping 70+ columns in a single table rather than having them in 2 tables by way of relationship. I am having a web registration page for a candidate. My team members are suggesting to keep all the columns in a single table and my argument with them is to have 2 tables.
Table 1 will consist of only those columns which are on the first screen registration (some of them are mandatory and some are not)
Table 2 will consist of other page columns which may or may not be filled by the candidate ie., he can fill up those columns if he wants to do so.
Performance wise it will be better to divide them into 2 tables rather than keeping in a single table.
I want your suggestions.
Lucky
April 28, 2004 at 12:03 am
Hi Lucky,
You are right. Performance and storage will be improved if you have two tables. Table 1 should contain all the mandatory fields, Table 2 should contain all of the other fields. Otherwise you will end up with a huge table filled with lots of NULLS. This will impact on indexing and storage.
You could also look at keeping varchars and chars in a seperate table and ints, numerics etc in the parent (original) table and creating a type of "Lookup" table to keep the varchar/char values with a record id to match it back to the parent table. This will also improve performance.
I think it would be a really bad idea to have 70+ columns in a table. I get worried when I see more than 15!
If you are the DBA, your collegues should trust your judgement when it comes to the database.
Good luck,
Angela
April 28, 2004 at 12:24 am
Normalize your data!!!
Coach James
April 28, 2004 at 3:06 am
indeed - you really need to look at the design.
get someone who knows something about databases, lok at your data flow, create a design and implement it.
cheers
dbgeezer
April 28, 2004 at 6:33 am
Lucky, I know how you feel, imagine that I have to work with a DB and one of the tables has 134 Columns (it stores Config data)!
Here's the reply I get when I said I could design it in 2 tables with maybe a Maximum of 5 Columns in each..."Well, we need to keep the design so that it's not easy for someone just looking at the database to figure out what's happening". HELLO, McFly!!! You named the Columns what they actually do! And they're ALL Bit Fields! I sometimes wonder which Cracker Jacks box people learn to design DBs in.
I mean, I do understand that sometimes you might want to De-Normalize, however, in your and my case, this is NOT true! Denormalization will do nothing but give you headaches! What's going to happen when they want to add another "Type" of entry to the Webpage?? Guess what...another Column!
April 28, 2004 at 6:43 am
Tymberwyld think yourself lucky Only 134 columns. I inherited a data infrastructure that is a nightmare, and I'm not in a position to be able to make changes. Let me just say that they asked me to replicate a table and when I tried to replicate it there was an error saying that the table could not be replicated at there are more than 255 columns, I checked and the table has 263 columns...now that's what I call normalization
April 28, 2004 at 7:16 am
Gadzooks!
Well, if you ever buy a program called "HEAT", watch out! For all of these so called "Yes, we can customize it to your Business needs" features, they add Columns, upon Columns of information in a certain table. Yes, it's very special .
Anyway, the one day a question was raised..."Can SQL Server have more than 1024 Columns??" ...I fainted...
April 28, 2004 at 7:56 am
Had a developer come up to me yesterday and tell me that he wants to add a varchar(8000) column to an already wide table....
April 28, 2004 at 10:10 am
OK AngelaBut,
I totally agree on what you all are saying.
What if the table holds very few records despite the many columns? Should performance still become an issue?
Will appreciate any of your thoughts please
April 28, 2004 at 11:42 am
Ok, this ALL depends on how Dynamic this needs to be. If you foresee these Columns as changing in the future, or that you'll need more columns later, then use 2 tables and structure it somewhat like an INI file (GlobalVars (Parent), GlobalParams (Params & Values)).
If this could never possibly change in a million, zillion years, then, 70 Columns might just be fine!
April 28, 2004 at 12:34 pm
So my 284 column table shouldn't be so bad??
Nothing like inheriting that and then having the owner tell me how it should be able to respond to almost any query in under 30 seconds (26Mil rows or approximately 25GB of raw data)
April 28, 2004 at 12:54 pm
In the immortal words of Dr. Evil..."RIIIIIIIGGHT"
April 28, 2004 at 1:15 pm
million, zillion years? that's quite a while...almost as long as it takes me to get up in the morning...
Is it just me, or does every dba have developer issues?
April 28, 2004 at 2:07 pm
To thgamble1 , I think you have to read what data modeling means. Because 284 columns in 1 table, thats huge. And I don't think it will improve your performance.
So normalisation is on the order, till a certain point. You don't need to normalise the whole structure. But like I already said 284 columns ?????
April 28, 2004 at 5:33 pm
Hi HerbieChin,
In regard to your question
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply