Number of fields in a table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
timclaason
Forum Commoner
Posts: 77
Joined: Tue Dec 16, 2003 9:06 am
Location: WI

Number of fields in a table

Post 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?
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post 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.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post 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
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post 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
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

califdon wrote:I hope the moderator moves this thread to Databases, though.
you know, that's not a bad idea.

Moved.
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post 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
timclaason
Forum Commoner
Posts: 77
Joined: Tue Dec 16, 2003 9:06 am
Location: WI

Bones Example

Post 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?
Post Reply