70+ columns in a single table.
-
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 -
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
-
Normalize your data!!!
Regards,Coach James
-
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
-
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!
-
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 
-
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...
-
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....
-
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
-
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!
-
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)
-
In the immortal words of Dr. Evil..."RIIIIIIIGGHT"
-
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?
-
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 ?????
-
Hi HerbieChin,
In regard to your question
I would say, it depends. If you only have 10 rows in the table you probably won't see any performance gain. If you had 100,000 rows you probably would.Performance tuning is a tricky thing and has to be constantly reviewed dependant on the data, the table structure, and the queries. When I replied to lucky's post, I was trying to give him a few different ways he could approach it, without knowing the actual data or table.This posting seems to have gotten quite off track. It would be good if someone posted some other suggestions to Lucky rather than complaining about the databases they have.Angela
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply