Page 1 of 1
Number of fields in a table
Posted: Fri Dec 15, 2006 10:13 am
by timclaason
I feel that a lot of times, database/table design is reasonably intuitive, and it's pretty easy to decide whether fields should be in an existing table, or if they merit creating a separate table.
However, sometimes it's not so clear.
I'm designing a Quality Assurance Testing Log for the company I work for, and in it, I have a table called "tbl_tests." tbl_tests has 18 fields in it, and I never really run into situations where I need a table with that many columns. But none of the columns really seem out of place in the table.
Does anyone here run into situations where they need that many columns, or is that so bloated that I should be rethinking the table design?
Posted: Fri Dec 15, 2006 11:03 am
by Ollie Saunders
18 fields in one table is verging on suspicion. Whilst it is possible that they are correct I can't be certain until you explain what they all are.
Posted: Fri Dec 15, 2006 12:43 pm
by onion2k
A table show have as many columns as are necessary, but no more. It's that simple. I've got tables in some databases with > 40 columns ... because they're needed.
Posted: Fri Dec 15, 2006 2:36 pm
by Ollie Saunders
errr.. 40?! You know, that really is too many. If nothing else performance is going to suffer.
Both of you might benefit from reading up and practising
database normalization
Posted: Fri Dec 15, 2006 2:51 pm
by Begby
The number of fields has absolutely nothing to do with normalization, and probably not much with performance if its written well. If you split it up into multiple tables and you still need all the data on every query, multiple tables will actually be slower.
You can have a table with 1000 fields and it can still be 3NF. All that matters is that every field depends on the key, the whole key, and nothing but the key. Basically if all the 40 fields are unique to that entity and only depend on the entity, then the table is fine.
Posted: Fri Dec 15, 2006 2:57 pm
by Ollie Saunders
The number of fields has absolutely nothing to do with normalization
Absolutely
You can have a table with 1000 fields and it can still be 3NF
Really? Ever seen one?
All that matters is that every field depends on the key, the whole key, and nothing but the key. Basically if all the 40 fields are unique to that entity and only depend on the entity, then the table is fine.
I seriously doubt you can find 40 fields for
anything that meet the requirements of even first normal form.
Posted: Fri Dec 15, 2006 3:09 pm
by RobertGonzalez
ole wrote:All that matters is that every field depends on the key, the whole key, and nothing but the key. Basically if all the 40 fields are unique to that entity and only depend on the entity, then the table is fine.
I seriously doubt you can find 40 fields for
anything that meet the requirements of even first normal form.
I disagree with you. I think Begby is right. We have tables in our enterprise application at work that have a large number of fields for them. The tables require those fields because each of the fields is uniquely associated with that rows id. There are no two ways around it. If the data for a record is unique to the record, then logically it should be in that record, regardless of the number of pieces of data are associated with that record.
In my own experience I've had a table that was 21 fields, and each of those fields literally only fit in that table.
Posted: Fri Dec 15, 2006 3:24 pm
by Kieran Huggins
It all depends on the data you're mapping.. you could have a table with 5,000,000,000 fields and be perfectly justified and in normal form.
I think what ole was driving at is that a wide table is suspect, and worth investigating. If you've looked and can defend the table structure, job done!
Cheers,
Kieran
Posted: Fri Dec 15, 2006 3:38 pm
by Begby
ole wrote:
All that matters is that every field depends on the key, the whole key, and nothing but the key. Basically if all the 40 fields are unique to that entity and only depend on the entity, then the table is fine.
I seriously doubt you can find 40 fields for
anything that meet the requirements of even first normal form.
I have a client that machines very specific parts. While looking at their database I noticed that some of their parts have tables with over 30 fields. Things like engine type, mount type, bolt pattern, bolt size, hp rating, material, pricing, sku etc. etc. etc. Almost all of the fields are foreign keys. Its most definitely 3NF. Forty is a definite possibilty. Imagine having to create a database with measurements of every bone in someones body. We are talking over 200 fields here and all would be unique to the record. It would be 3NF.
Posted: Fri Dec 15, 2006 3:44 pm
by onion2k
Kieran Huggins wrote:I think what ole was driving at is that a wide table is suspect, and worth investigating.
Nope. Ole said:
errr.. 40?! You know, that really is too many. If nothing else performance is going to suffer.
Both of you might benefit from reading up and practising database normalization
He is stating as a fact that you should never have more than 40 fields in a table. There's no ambiguity, he isn't saying it's suspect, he's stating a very clear opinion.
He's wrong.
Posted: Fri Dec 15, 2006 5:16 pm
by califdon
Oh dear, I just have to throw in my two cents worth here. Begby is 100% correct. Having taught database and other computer courses in college for a decade and developed many commercial database applications, it is very easy for me to imagine tables with dozens or more columns. Indeed, of all the tables I have worked with in many different applications, tables with 25 or more columns are quite common. Relational database theory, as developed by Ted Codd, is based on mathematical set theory, and is entirely based on normalization rules, which depends exclusively on how attributes relate to entities. The number of fields in a table is completely immaterial.
I hope the moderator moves this thread to Databases, though.
Posted: Fri Dec 15, 2006 6:28 pm
by Ollie Saunders
He is stating as a fact that you should never have more than 40 fields in a table. There's no ambiguity, he isn't saying it's suspect, he's stating a very clear opinion.
It would seem I'm stating very clear ignorance more than anything else. I can't understand how 40 fields in 3NF could occur but a lot of you have said I'm wrong and I'm not particularly big on databases so I stand corrected.
This is an eye opener for me. I would be interested to see the schema for any of these super-tables.
Edit: Didn't notice this...
Imagine having to create a database with measurements of every bone in someones body. We are talking over 200 fields here and all would be unique to the record.
that is a good point.
Posted: Fri Dec 15, 2006 6:36 pm
by RobertGonzalez
califdon wrote:I hope the moderator moves this thread to Databases, though.
you know, that's not a bad idea.
Moved.
Posted: Sat Dec 16, 2006 11:02 pm
by fractalvibes
Just look at some of the system catalog tables for our RDBMS and you will see some have a quite a few fields.
The 'width' of table has no bearing on normalization so long as they belong in the table. You do of course want to be selective in your queries and try not to do select * from someTable....
Anywhere from 20 columns to several dozen is quite normal for complex business data.
fv
Bones Example
Posted: Thu Dec 21, 2006 8:48 am
by timclaason
As far as the example of a bones table goes (with more than 200 bones in the body), I guess if I were designing a table that had descriptions of bones or something else where every bone would have some kind of description associated with it, I might be inclined to break that up to prevent there from being so many tables:
This is how I would design the schema (I put some examples in) to minimize table fields
tbl_bones:
Code: Select all
boneid bodyarea bonename description
------------------------------------------------------------
1 1 femur This is the femur
2 1 tibia This is the tibia
3 1 fibula Description for the fibula
4 2 humerous This is the humerous
5 3 sternum Description for the sternum
Then I would have a table with body areas that joins bodyarea in tbl_bones with bodyareaid in tbl_bodyareas.
tbl_bodyareas:
Code: Select all
bodyareaid bodyareadescription
----------------------------------------------
1 leg
2 arm
3 chest
Then I could do joins on tbl_bones boneid field.
I know the bones was just an example and I'm probably going off on a tangent here, but I always try to come up with ideas to minimize the number of fields in a table.
Is the general consensus that the way I do it is not industry standard or optimal?