How many fields is too much? (1 Viewer)

I have a table for quotes tblQuote. It has
QuoteNum
Descript
CustID
SalesID
MaxPartLength
MaxPartHeight
MaxPartWidth
MaxPartWeight
PlantCity
PlantState
PlantCountry
and a slew of other aspects of the quote. It originally had 110 fields. I pulled out 50 of them and placed them in other tables. I have 60 fields left in tblQuote.

I can quite easily see where I can pull out other fields from tblQuote and create a new table for them (EG: MaxPartLength, MaxPartHeight, MaxPartWidth, MaxPartWeight - put these into a new tblPartDimensions).

But at what point to I start to create too many tables by doing this? My database already has 16 tables linked to the table tblQuote. I can probably pull out another three sets of data from that table to create three new tables linked to tblQuote. But will that get to a point that it is really difficult to manage with table relationships and building query/reports? I have many more tables to create yet and link to tblQuote (aside from what is already in tblQuote and the other existing tables). I am less than half-way done with data tied to the quote. Is it crazy to have dozens of tables linked to tblQuote?

Although it will make sense to me to keep pulling out the groups of fields into separate tables, will future developers curse my name and send me hate mail? How does that affect (negative or positive) the database performance?

Or is pulling out the other fields into new tables exactly what I should be doing - shut up and keep going?

the objective with tables is to keep all 1 to 1 related data together in the same record. If this means 150 fields, so be it. Having said that if you have groups of fields which are rarely used, have requirements for data security or are mutually exclusive then these can be held in other tables. The main benefit of keeping tables 'narrow' is for performance in that there is less data to transfer across the network.

without knowing the detail of your data or your business, it is impossible to advise but it sounds like you have one big record to cater for all eventualities. Most quotes would consist of a number of tables - customers, perhaps addresses, quote header/footers, line details, products. From your example this would not appear the be the case - I can see you have split out customer, but not address for example

PlantCity
PlantState
PlantCountry

These should be in a "Plant" table. You would have the ID for the plant in your quote and pull this information as needed. If it is possible you would have more than one plant involved with a single quote you would have to put in linking records to you can have a many to many relationship.

Big advantage, this lets you include information regarding the plant other than its location in your file, such as name, POC, and contact number.

Thanks - I appreciate the help. The reason I started to question my approach is that I see many posts of someone asking for help who has a lot of fields in a table and the first response is almost always something along the lines of "Your table has too many fields and so I suspect it is designed poorly." or "You need to normalize your database."

The quotes are for complex industrial systems that have hundreds of components (thousands of parts) and a few dozen people involved in design, programming, manufacturing, travel, etc... - all of which must be accounted for in the final quote. The final quote may be 40 - 100 pages long. I realize now that I pulled out a lot of fields that were a one-to-one relationship with the tblQuote, so I moved them back into the table. Seems that in this case, a table with many fields is the nature of the beast.

as mentioned before, without knowing your design it is impossible to say whether you have it right or not.

The comment 'have hundreds of components (thousands of parts) and a few dozen people involved in design, programming, manufacturing, travel, etc' implies components should be in a components table, parts in a parts table and people among a variety of tables depending on function. I presume you are already doing this

Good luck with your project.

CJ_London said:

as mentioned before, without knowing your design it is impossible to say whether you have it right or not.

The comment 'have hundreds of components (thousands of parts) and a few dozen people involved in design, programming, manufacturing, travel, etc' implies components should be in a components table, parts in a parts table and people among a variety of tables depending on function. I presume you are already doing this

Good luck with your project.

Click to expand...

Yes - I have the large components split out into separate tables and parts of those components split into other tables and labor split out as well. It is a mammoth task so I am constantly looking for ways to improve it. Thanks for your input.

The question reminds me of the exchange in the movie Amadeus between Mozart and the Duke (exact name escapes me at the moment) in which the Duke says to Mozart, "You have too many notes" and Mozart replies, "Very well, which ones shall I take out?" And of course, the answer is that ALL of the notes contribute to the composition as a whole - so don't remove ANY.

In any case, getting the normalization right is a difficult process. Some of your fields jump out at me because there is a possible issue with including them in the main table. You have already mentioned a couple of cases but didn't enumerate the 60 fields. Just as well that you didn't because without understanding the fields, we might not be able to do much for you. But I can show you how to ask the question.

We can talk about cases where you have to duplicate data across two records in the same table or cases where you have a numbered group of fields in a single record, things like that. However, the rule that you must consider for keeping something IN your main table or moving something OUT of your main table is really based on dependency.

The rule is that you must look at the PK for the table and then determine the dependency for the field you are considering. To keep a field in a table, that field must depend entirely and only on the PK (and nothing else).

So the fields describing "plant" attributes are immediately suspect. One could imagine a set of multiple quotes that would be handled (processed? built?) at the same plant. So that means the details of plant location don't depend on the quote number, they depend on a plant ID code or number. So they don't belong in the main table. An assigned plant ID would belong, but the plant details can go into a plant details table keyed by plant ID.

A contrary example would be the customer ID or assigned plant ID, both of which would be unique per quote. The details of the customer or plant would NOT be in the main table but the ID codes would because those things depend entirely and only on the quote number.

This is the kind of question you should be asking yourself. Now note that I have not actually discussed how to tell which kind of relationship you need. However, the only two cases likely to be revealed by this line of inquiry are many-to-one (or one-to-many, the opposite view of the mirror) and many-to-many (a junction table).

This probably didn't tell you any fields you hadn't already considered - but I might have at least told you WHY certain questions should be asked regarding dependency. That might help you to whittle down your field list.

Another rule (which you are indirectly hinting at) is to reduce the number of components in your database. It's easy and common for people to create three forms when one form will do. What usually happens is you have a form viewing the data, then a copy of that form is made for entering data and another copy for editing the data. Now you have three forms when all you really need is one form and some VBA code to modify it slightly for the different purposes.

The same approach can be applied to tables. Imagine you have a table for looking up Town names, you have another table for looking up County names, and another for looking up Country names. These are referred to as lookup tables. Simplest version of such a table would be an ID field and a text field. As you can see, in essence the 3 suggested tables are identical apart from the information they store. Therefore it is possible to create a single table which would have three Fields the ID field and the description field and an extra field to identify whether the data is Town County Country.

One very good reason for doing this is now you only need one form for displaying/editing/adding either the town county or country list.

However I warn against doing this too early in your database construction, except for the obvious lookup tables. If you do it to early you can find yourself having to undo it when you discover you need to add extra fields to particular tables for some reason.

Sent from my SM-G925F using Tapatalk

As others have stated, it's hard to be prescriptive. You can't (shouldn't) arbitrarily split a table into 2 just because there are too many columns.

What you have to do, really, is analyse and consider every data field that you require within your database, and then decide on the appropriate structure to hold them in order to avoid repeating data (or repeating groups of data).

With experience a lot of this becomes instant - it's just part of the developer's experience, but even then, you can often find that you need to rework some aspects.

Take the fields you supplied

QuoteNum
Descript
CustID
SalesID
MaxPartLength
MaxPartHeight
MaxPartWidth
MaxPartWeight
PlantCity
PlantState
PlantCountry

Others have pointed out that the plant details belong in a table of their own (otherwise, you will most likely have several orders where the values for the 3 plant fields are identical) - these are repeating groups.

I also can't understand the relevance of the 4 "max" fields. Maybe they need to be in the table, but maybe they don't.


Access is a complicated beast. In the end it's far quicker to manage data with a database than it is with a spreadsheet, but it's also rather harder to get to grips with for a new user. Users can become quite proficient with spreadsheets without ever writing a macro, or code. You cannot possibly achieve anything much in Access without a lot of code. In fact a lot of code is actually defensive, to prevent your users doing "whatever" they like in access, because access is so powerful.


Also in Excel, you can see all of your data. In Access, you rarely see everything. You only see a subset of data corresponding to the columns and rows that interest you for the moment. Becuase of this, you need to be 100% confient that your app is managing all the data correctly (including the bits you can't see)

Anyway, Good luck with your project.

Uncle Gizmo said:

Another rule (which you are indirectly hinting at) is to reduce the number of components in your database. It's easy and common for people to create three forms when one form will do. What usually happens is you have a form viewing the data, then a copy of that form is made for entering data and another copy for editing the data. Now you have three forms when all you really need is one form and some VBA code to modify it slightly for the different purposes.

The same approach can be applied to tables. Imagine you have a table for looking up Town names, you have another table for looking up County names, and another for looking up Country names. These are referred to as lookup tables. Simplest version of such a table would be an ID field and a text field. As you can see, in essence the 3 suggested tables are identical apart from the information they store. Therefore it is possible to create a single table which would have three Fields the ID field and the description field and an extra field to identify whether the data is Town County Country.

One very good reason for doing this is now you only need one form for displaying/editing/adding either the town county or country list.

However I warn against doing this too early in your database construction, except for the obvious lookup tables. If you do it to early you can find yourself having to undo it when you discover you need to add extra fields to particular tables for some reason.

Sent from my SM-G925F using Tapatalk

Click to expand...

For all lookups, I use a similar concept;
ID - PK, Auto number
Sort - Which lookup(s) will be using this
SortValue - Value that goes into field using this lookup
SortValueDesc - Description (if needed) for value
SortOrder - OPTIONAL, used when you want to manually assign sort order.

From one database I am using, my data looks like this:
"21" "Tag" "LVN to RN" "Applied under the LVN to RN program" "6"
"22" "Tag" "Transfer" "Transferred in from another college/university" "7"
"23" "Tag" "Hold" "Student is or was in a hold status, check notes" "8"
"24" "Tag" "R-246" "Student repeated NURS 246" "1"
"25" "Tag" "R-NP1" "Student repeated NP1" "2"
"26" "Tag" "R-NP2" "Student repeated NP2" "3"
"26" "Tag" "R-NP3" "Student repeated NP3" "4"
"26" "Tag" "R-NP4" "Student repeated NP4" "5"

Since I have queries that sort based on [sort], when putting tags on students (since more than one may apply) the WHERE is [sort] = "Tag" and the ORDER BY is [SortOrder].

This gives a list to the end user that looks like
R-246
R-NP1
R-NP2
R-NP3
R-NP4
LVN to RN
Transfer
Hold

Since most often we will be tagging a student for repeating a class, this makes for a more natural approach.

Same table holds values for multiple lookups, including the list of lookups allowed in the database.

There is no way to normalize your data other than attribute by attribute. Each one needs to be examined and specific questions must be asked.
Does this attribute depend on the table's primary key, the whole key and nothing but the key?
Does this attribute occur more than once for the primary key?

The plant fields fail test one since they depend on plant ID rather than Quote ID. If multiple quotes are fulfilled at the same plant, the values of those fields will be the same. That tells you that you need to create a plant table to isolate plant attributes so that they occur once and only once.

I didn't see any examples of repeating groups in your list of fields but look for names with suffixes - Addr1 and Addr2 are not a repeating group. They are the same field split into two to accommodate a print format. You could use an embedded CRLF but it is easier for users to simply break the data into how it should appear on an envelope. In applications that handle bulk mail, you will find that address is actually broken down into several fields such as houseNumber StreetType (St, Rd, Ln, etc), CompassDirection ( E, W, NE, etc.), etc. This allows for much better de-duping code. Since most applications don't care about address de-duping, they settle for simple print formatting.

In the case of repeating groups be especially aware of things that are different but fall into one category. In an application that analyzes rental property, expenses is such a group. You might start with Heat, Electric, LawnMaintenance, PropertyTaxes, etc. but, then you acquire a new building and find you also need Elevator and PoolMaintence. Separating these into a 1-m table where the table can add a row if a new expense type is identified makes for a solid solution and simpler SQL.