Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
One big table or a few smaller oned
- Thread starter NNNNN
- Start date
johnherman
MIS
In general it is easier to parse a row than to join a table.
-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright
-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright
it depends....
if all 30 fields(columns) are about a SINGLE object, then there's no problem.
For instance, if all the fields relate to a PERSON, then they should all be in the same table. If however, you have information about the PERSON and all the COURSES they are enrolled in, in a single table, then your database is not normalized.
Check out The Fundamentals of Relational Database Design for more information on normalization and how to normalize your database.
Leslie
if all 30 fields(columns) are about a SINGLE object, then there's no problem.
For instance, if all the fields relate to a PERSON, then they should all be in the same table. If however, you have information about the PERSON and all the COURSES they are enrolled in, in a single table, then your database is not normalized.
Check out The Fundamentals of Relational Database Design for more information on normalization and how to normalize your database.
Leslie
aardvark92
Programmer
I would just add that you'd have to have at least one index column in common among the three tables, or you wouldn't be able to join them. And unless you can guarantee that there are no values duplicated in any of the columns, this index column will be in addition to the real data. So, the question becomes: Is it better to have one table with 30 columns or 3 tables with 11 columns each?
If you have an existing column you can use for an index, you'll still have to copy it to the other tables, resulting in one table with 10 columns and two tables with 11 columns each.
If you have an existing column you can use for an index, you'll still have to copy it to the other tables, resulting in one table with 10 columns and two tables with 11 columns each.
Usually the single-table approach is preferred but there are instances where you may want to split some fields to different tables.
An obvious example is basic data about an employee in one table but payroll-related stuff that has different security requirements in another. Usually the business requirement will dictate this more than the inner mysteries of relational database design (i.e. it's a physical problem or a problem whose controlling parameters are outside the scope of the application.)
An obvious example is basic data about an employee in one table but payroll-related stuff that has different security requirements in another. Usually the business requirement will dictate this more than the inner mysteries of relational database design (i.e. it's a physical problem or a problem whose controlling parameters are outside the scope of the application.)
As lespaul stated you need to look at what fields you have . Aardvark92 states index another good point. It is not a yes or no question without knowing ALL the facts
Never give up never give in.
There are no short cuts to anything worth doing
Never give up never give in.
There are no short cuts to anything worth doing
Another point to consider is how often you need the information in the columns and what information is accessed together. If you are going to separate the tables (or must because you have reached the record size limit) then you need to consider which information logically will be queried together. Try as much as possible to keep this information in the same table. This will reduce the number of joins in queries. But it also means that splitting up the exact number of columns into each table is unlikely. You may need 17 in one table and 14 in another. Keep the most frequently accessed information in the main table.This should reduce the number of queries that need to be rewritten becasue the tables were split.
It might also be necessary to use subforms to group the information being updated or inserted into the tables they will go to.
Also if you feel you must split the tables, then consider a view with the tables joined. If this has the same name as the origianl table, then you may have less code to adjust after performing the split.
Finally if you have fields like Price1, Price2, Price3 making up the columns, then you definitely need to split this into a related table. This is a one to many relationship as opposed to a one-to-one relationship that the other type of table splitting would have.
Questions about posting. See faq183-874
It might also be necessary to use subforms to group the information being updated or inserted into the tables they will go to.
Also if you feel you must split the tables, then consider a view with the tables joined. If this has the same name as the origianl table, then you may have less code to adjust after performing the split.
Finally if you have fields like Price1, Price2, Price3 making up the columns, then you definitely need to split this into a related table. This is a one to many relationship as opposed to a one-to-one relationship that the other type of table splitting would have.
Questions about posting. See faq183-874
NNNNN
A late comer...
Why are you asking this question -- what is your objective?
The biggest issue for the most part is to realize why you want to use a "Relational" database. By using tables, you reduce duplication of data. Instead of having the phone number of a contact in three or four locations, have it in one location. Why?
Easier maintenance. The person changes their phone number and now you have to track all three references and edit them. This is why the older, flat file database were so frustrating -- prone to error.
Less space. How much really depends since you have to trade-off the space used by the foreign key, and the space used by indexes.
Please review some known and valued references on the web for a more authoratative review...
Fundamentals of Relational Database Design by Paul Litwin
Download document
Read on-line (HTML)
Micro$oft's answer...
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database
...Moving on
There are times when you may have to duplicate data, or break data that can fit on one table into two or more tables.
As indicated by SQLSister, to speed access to a frequently accessed child table. For example, you frequently access InvoiceDetail information. Usually, you have the InvoiceDate on the Invoice master or header table. But your specific requirements, perhaps sales of inventory by date reports, you need to frequently "hit" the InvoiceDetail table. In this case, it may make sense to add the InvoiceDate to InvoiceDetail table design to accommodate this requirement -- it breaks the rules of Normalization, but this type of approach may help address a performance issue.
The other requirement is for security. A very typical example would be to break off the personal HR information (salary, performance reviews) into one "private" table and the general (name, phone number) "public" information into another table.
Then, as indicating. Look at your indexes. Index what fields are frequently used in queries. But do not over-index. Indexes take up space, and will slow down the time it takes to add or delete records. A funny story... A long time ago, the "shop" I was working with di dnot index the phone number of the contacts. At a demo for the application, somebody asked to run a query by the phone number -- 20 min later, the query was still running (thank goodness for Unix so the sales guy could switch to another view). We indexed the table for phone number over lunch and then reviewed the demo question -- the results displayed in less than a second.
To recap...
- Normalize to reduce duplication of data. Saves big-time on maintenance, and may save space.
- Look at the demands of your database.
-- Where do you need indexes
-- Are there areas where you do need to break the rules for "Normalization"?
Richard
A late comer...
Why are you asking this question -- what is your objective?
The biggest issue for the most part is to realize why you want to use a "Relational" database. By using tables, you reduce duplication of data. Instead of having the phone number of a contact in three or four locations, have it in one location. Why?
Easier maintenance. The person changes their phone number and now you have to track all three references and edit them. This is why the older, flat file database were so frustrating -- prone to error.
Less space. How much really depends since you have to trade-off the space used by the foreign key, and the space used by indexes.
Please review some known and valued references on the web for a more authoratative review...
Fundamentals of Relational Database Design by Paul Litwin
Download document
Read on-line (HTML)
Micro$oft's answer...
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database
...Moving on
There are times when you may have to duplicate data, or break data that can fit on one table into two or more tables.
As indicated by SQLSister, to speed access to a frequently accessed child table. For example, you frequently access InvoiceDetail information. Usually, you have the InvoiceDate on the Invoice master or header table. But your specific requirements, perhaps sales of inventory by date reports, you need to frequently "hit" the InvoiceDetail table. In this case, it may make sense to add the InvoiceDate to InvoiceDetail table design to accommodate this requirement -- it breaks the rules of Normalization, but this type of approach may help address a performance issue.
The other requirement is for security. A very typical example would be to break off the personal HR information (salary, performance reviews) into one "private" table and the general (name, phone number) "public" information into another table.
Then, as indicating. Look at your indexes. Index what fields are frequently used in queries. But do not over-index. Indexes take up space, and will slow down the time it takes to add or delete records. A funny story... A long time ago, the "shop" I was working with di dnot index the phone number of the contacts. At a demo for the application, somebody asked to run a query by the phone number -- 20 min later, the query was still running (thank goodness for Unix so the sales guy could switch to another view). We indexed the table for phone number over lunch and then reviewed the demo question -- the results displayed in less than a second.
To recap...
- Normalize to reduce duplication of data. Saves big-time on maintenance, and may save space.
- Look at the demands of your database.
-- Where do you need indexes
-- Are there areas where you do need to break the rules for "Normalization"?
Richard
- Status
Similar threads
- Locked
- Question
- Locked
- Question