Number of fields in a table
Moderator: General Moderators
-
timclaason
- Forum Commoner
- Posts: 77
- Joined: Tue Dec 16, 2003 9:06 am
- Location: WI
Number of fields in a table
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?
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?
- Ollie Saunders
- DevNet Master
- Posts: 3179
- Joined: Tue May 24, 2005 6:01 pm
- Location: UK
- Ollie Saunders
- DevNet Master
- Posts: 3179
- Joined: Tue May 24, 2005 6:01 pm
- Location: UK
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
Both of you might benefit from reading up and practising database normalization
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.
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.
- Ollie Saunders
- DevNet Master
- Posts: 3179
- Joined: Tue May 24, 2005 6:01 pm
- Location: UK
AbsolutelyThe number of fields has absolutely nothing to do with normalization
Really? Ever seen one?You can have a table with 1000 fields and it can still be 3NF
I seriously doubt you can find 40 fields for anything that meet the requirements of even first normal form.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.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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.ole wrote:I seriously doubt you can find 40 fields for anything that meet the requirements of even first normal form.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.
In my own experience I've had a table that was 21 fields, and each of those fields literally only fit in that table.
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
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
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
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.ole wrote:I seriously doubt you can find 40 fields for anything that meet the requirements of even first normal form.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.
Nope. Ole said:Kieran Huggins wrote:I think what ole was driving at is that a wide table is suspect, and worth investigating.
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.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's wrong.
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.
I hope the moderator moves this thread to Databases, though.
- Ollie Saunders
- DevNet Master
- Posts: 3179
- Joined: Tue May 24, 2005 6:01 pm
- Location: UK
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.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.
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...
that is a good point.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.
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
-
fractalvibes
- Forum Contributor
- Posts: 335
- Joined: Thu Sep 26, 2002 6:14 pm
- Location: Waco, Texas
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
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
-
timclaason
- Forum Commoner
- Posts: 77
- Joined: Tue Dec 16, 2003 9:06 am
- Location: WI
Bones Example
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:
Then I would have a table with body areas that joins bodyarea in tbl_bones with bodyareaid in tbl_bodyareas.
tbl_bodyareas:
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?
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 sternumtbl_bodyareas:
Code: Select all
bodyareaid bodyareadescription
----------------------------------------------
1 leg
2 arm
3 chestI 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?