Max number of Fields in a Table

[Andrzejek]

Oracle table, real world application, not an academic / theoretical approach.

What would be / should be the maximum number of fields in a table?
I know most of look-up tables have 2, 3, 4 fields. But I am talking about tables that hold actual data.

What’s the ‘workable’, ‘manageable’ max size? Is 50 fields too many? 100? 200+?

I’ve seen here at TT an opinion of 20 fields, no more *. First I said: No way! Then, when I thought about it for a while, I changed my mind and said: Yes, that sounds like a reasonable number for (almost *) all cases.

*unless it could be proven in some situations where it is necessary to have a little more fields


Have fun.

---- Andy

 

[papadba]

The number of columns should be be determined by the (proper) design of the database.

Sometimes 2 columns is all that are needed and sometimes 20 do not meet the need.

Some organizations inappropriately establish this kind of "rule", i'm not aware of software vendors doind this (unless there was some product limitation).

 

[tcsbiz]

I have long had the opinion that 30 fields is about the maximum for most any database table. However, that isn't a hard and fast rule. I use it as an indicator that I should review the design. Maybe there is a need for a single table to have many more fields. I just want to take another look at it to make sure the design makes sense.

Tom

I keep trying to do something about my procrastination but I keep putting it off until tomorrow.

 

[Olaf Doschke]

I would also judge a table as wrong design, if it as 5 fields, but among hem are address1, address2, address3, or privateemail, businessmail or brotherid, sisterid, or child1id, child2id.
You have to understand table normalization and why it's done and what it's good for, it's not a matter of quantity, it's a matter of quality. A rule of thumb to look into a table, if it has too many fields just results from the normalization rules but it's not vice versa.

Bye, Olaf.

 

[mmerlinn]

If, for example, you have a table of part numbers, you would generally want all information that SPECIFICALLY applies to that part number (like dimensions) in the same table unless a field could contain more than one value for that part number. In that case those fields usually should be put in a related table.

Depending on the type of part information that you would have, you could easily exceed 30 fields. Splitting a table just because it exceeds what you think is a maximum could easily create a lot of extra useless work for you.

I program in FoxPro. So I took a look at some of the tables FoxPro uses to hold screen data to build the screens. FoxPro SCXs (tables) are 79 columns wide. I assume that Visual FoxPro tables have even more columns.

I also looked at one of my main tables. It has 59 columns, none of which can easily be separated from that table. And it is linked to multiple other tables with data that does not fit the schema of the main table.

Bottom line is that the only limits are what is reasonable to hold the data needed that directly applies to the table in question. Picking an arbitrary number has the potential of increasing your work load and increasing the complexity of the table unnecessarily.

mmerlinn

http://mmerlinn.com

Poor people do not hire employees. If you soak the rich, who are you going to work for?

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Raymond

 

[Andrzejek]

OlafDoschke,
Your statement:”if it as 5 fields, but among them are […] privateemail, businessmail” got me thinking…
Would you recommend, instead of additional fields in a table, to build another table(s)?
Something like:

E_MAIL_STUFF table:
1 Private E-Mail
2 Business E-Mail
3 FaceBook

And a table to hold it all:
E_MAIL_ACCOUNTS
123 2 bgates@msn.com
123 1 bill_g@home.com
765 1 MyPrivateMail@gmail.com
765 2 MeAtWork@MyCompany.com


Have fun.

---- Andy

 

[MarkSweetland]

I would. If a data element occurs more than once, move it to a new table to hold that information. The new table will contain a foreign key that references the primary key of the old table.

EMAIL_TYPE TABLE
EMAIL_TYPE_ID ADDRESS
1             Private Email
2             Business Email
3             Facebook

EMAIL_ADDRESS TABLE
EMAIL_ADDRESS_ID EMAIL_TYPE_ID PERSON_ID EMAIL_ADDRESS
1                1             123       bill_g@home.com
2                2             765       MyPrivateMail@gmail.com
3                2             765       MeAtWork@MyCompany.com

The EMAIL_TYPE_ID is a Foreign Key to the EMAIL_TYPE Primary Key(EMAIL_TYPE_ID) and PERSON_ID a Foreign key to your user table Primary Key (PERSON_ID)



Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[Andrzejek]

Thank you Mark,

This may be a little off topic, but I was researching the definition of a (relational database) table, what makes an object in the database a table, and this is what I found:

In relational databases and flat file databases, a table is a set of data elements (values) that is organized using a model of vertical columns (which are identified by their name) and horizontal rows, the cell being the unit where a row and column intersect. A table has a specified number of columns, but can have any number of rows . Each row is identified by the values appearing in a particular column subset which has been identified as a unique key index.

Now, can the object be consider a ‘table’ if it does not have a unique key index (a Primary Key)? And going further with it, other (so called) tables do not have a Foreign Keys referencing the previous ‘table’.

I am not talking about the LOG table, for example, where I keep the information of WHO uses my application, when they get IN and OUT. I’m talking about the ‘design’ where the programmer, not the database itself, has to know how to relate the objects (tables?) in the database, which field relates to what field in other object (table?). Can such objects be really called ‘tables’?


Have fun.

---- Andy

 

[CajunCenturion]

==> Now, can the object be consider a ‘table’ if it does not have a unique key index (a Primary Key)? And going further with it, other (so called) tables do not have a Foreign Keys referencing the previous ‘table’.
Yes, it is still a table. It may not be a relational table, nor a normalized table, but it's still a table.

Tables existed long before relational database theory. Set theory based tables databases didn't have primary keys as set members were connected via embedded linked lists. Or you could have a table which represents a binary tree and you traverse that table through pointers, not keys.

--------------
Good Luck
To get the most from your Tek-Tips experience, please read
FAQ181-2886
Wise men speak because they have something to say, fools because they have to say something. - Plato

 

[Andrzejek]

Thank you CajunCenturion,

Just to be clear, I was referring to the ‘tables’ created now (not by me), in Oracle relational data base. And a LOT of them, too. I just have to work with them, I have nothing to do with setting them up. :-(

Have fun.

---- Andy

 

[Olaf Doschke]

Hi Andy,

> Would you recommend, .... to build another table(s)?

Mark has answered that, I would also have named the table EMAIL_TYPE or EMAIL_TYPES.

In regard to the definition you gave, technically most database systems don't force you to define that primnary key, but it's of course good practice. There is another thing which strongly differs in database tables and eg Excel Tables or Word Tables: A table field doesn't only define a caption for a column of the table. All rows have to have similar type. You can partly undermine that principle, because a text field can of course also contain the text representation of a number, date, or whatever other other type. But that aside a database mainly differs from a general purpose table formatting of rows and columns, in that you store identically structured records.

There is another perspective you can have about application design, starting from a class, like eg a person class, having all the properties of a person, eg including privatemail and businessmail. There are application frameworks or data acess frameworks supporting to desing your application starting from there and auto-generating database tables for persisting objects (class instances) by rather serialisiing them to XML or non normalized tables. So whatever you see there in the ORACLE database you have to work with might come from such a design.

With Microsoft dotNET and Entity Framework, there is such an approach called "Code first". Here's a sample of that, which nevertheless ands in a good normalised database schema in step 4:

http://weblogs.asp.net/scottgu/arch...irst-development-with-entity-framework-4.aspx

Bye, Olaf.

 

[ChrisHunt]

To answer the original question, there probably is a maximum number of columns allowed in a table for any given RDBMS - but it's not a limit you're ever likely to run into. The limit for an Oracle 11g table is 1000 columns (see

http://docs.oracle.com/cd/B28359_01/server.111/b28320/limits003.htm

). If you're running close to that kind of number, it should be ringing alarm bells about your design!

The short rule is that if you have repeating groups in your table - columns called item1, item2, item3 - they should probably be split out into a separate item table. The longer corollary is that sometimes it's better to leave them be, depending on the nature of the data and how you intend to use it.

Let's look at something that's already been alluded to - mailing addresses. Let's assume that any address in the world can be written in (at most) 5 lines of text; what's the best way to store the address of a customer? (Let's assume that for some reason you can't just store it in a single column with line breaks embedded where you need them)

Here's a few options (there are others):
[ol][li]We could put 5 columns into the customer table - address1, address2, address3, etc.[/li]
[li]We could add a new table [tt]customer_address_line[/tt], with columns [tt]customer_id[/tt], [tt]line_no[/tt] and [tt]address_line[/tt].[/li]
[li]We could add an [tt]address[/tt] table (with the address lines stored either within the table or in a further denormalised table, as above), and just add an [tt]address_id[/tt] to the customer table.[/li][/ol]

Option 1 is the simplest, but violates the "short rule" given above. However, if you think about how you might use this information, it looks pretty good. You're likely to capture the whole address at once, through some form of data entry; and use it all at once - say by printing a mailing list. The fact that all the address is in the same database row is very handy.

Option 2, which might appear to be the more "proper" solution, could be a fiddle to implement. Imagine writing a query to populate a mailing list, where you have to attach between 1 and 5 address_line records to each customer row. It's a lot more complicated for no great benefit.

Option 3 is useful if you want to do more with addresses than use them to send stuff through the mail. If you want to know whether 2 of your customers share an address, or you want to know where a particular address is geospatially, this could be the approach to take. Note also that you may be able to make use of ready-made address tables - the PAF or the NLPG in the UK, for example - which can improve the accuracy of your customer data.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[stefanhei]

The place you'll find address1, address2, etc. is most often a table that stores some action done in a specific point of time, like a sales table.
Addresses (email or geographical) of customers change over time and it is important to know what address you delivered your product to and where you sent your bill when the purchase was done, so a reference to the customer table is not sufficient.
Of course this could be normalized, but I've seen very few real world oltp applications that really did.

 

[Olaf Doschke]

Postal addresses are a very hard case of normalization, the more areas worldwide you need to be able to store and put together. Actually it would be a case for standardization, but putting that aside, more natural columns for an address storage are "county", "state", "city" + "postal code", "street" and "house number", plus several others. Also the case to store addresses as they were, when you sent an order or invoice, is partly more the job of a document management system of invoices and delivery bills etc. and partly a job of logging.

I know a developer coming from an insurance company which worked with ids in that case, too. Additional to that id a timestamp of the invioces or other documents is stored and the normalized address data is stored with a timerange of it's validity, so you can query for an address, as it was at a certain timestamp, you always create a touple of such data with one table for the current state and a historic table for earlier states of the data. This way you don't have to copy an address into each and any document record.

So even this is not a sample which you can't solve by normalization plus data history. It works like a charm, because if the address data changes you still can leave it with the same ID. Your sql queries will get more complicated, but it's not impossible to handle this way.

There's another side of this: If you're not a bigger company with the need of an optimal storage and absolute precise handling of data, you will tend towards less normalized data storage. Taken to a practical level, if you need to store a list of your close family with telephone number and adresses, you may even not use a database table at all, and if you do, you'd perhaps store everything in one table with such "errors" as home and office mailadress. A perfected data structure then is in the way of easy data browsing.

Bye, Olaf.