If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Should I break up a big table or not?
In the middle of database design and need some advice - I am unsure if I
went down the right path... In my DB, each customer has lots of information - fields. Each customer has contact info, Billing contact info, Device configuration info - about 80 fields in all I started by breaking these logical units into separate tables to keep each table manageable. I have a form based on a query that joins all the above back into a recordsource All are linked by CustomerID and are 1:1 relationships Problem is if fields are skipped during creating a new customer, and a table in the join does not get any field, then no record is created in that table. So, if I create a new customer and do not bother with any Billing info, then Billing table will not get a new record. I suppose, I could add code and checks to force data into each table before an update of a new customer record. But now I am wondering if I should just keep it simple and sweep everything back into one 80 field table. Is it worth the hassle to basically partition a 80 field table into smaller parts and have to write code to make sure each table gets a new record? Opinions please Bill |
#2
|
|||
|
|||
All are linked by CustomerID and are 1:1 relationships
There is no need to split anything that is 1:1. But I would think that you would have more than one order with a customer. Billing address can be in the customer table but the amount billed needs to be in the order or payment table. You do not need code when you split the table. Just use an append query to populate the tables. "Bill" wrote: In the middle of database design and need some advice - I am unsure if I went down the right path... In my DB, each customer has lots of information - fields. Each customer has contact info, Billing contact info, Device configuration info - about 80 fields in all I started by breaking these logical units into separate tables to keep each table manageable. I have a form based on a query that joins all the above back into a recordsource All are linked by CustomerID and are 1:1 relationships Problem is if fields are skipped during creating a new customer, and a table in the join does not get any field, then no record is created in that table. So, if I create a new customer and do not bother with any Billing info, then Billing table will not get a new record. I suppose, I could add code and checks to force data into each table before an update of a new customer record. But now I am wondering if I should just keep it simple and sweep everything back into one 80 field table. Is it worth the hassle to basically partition a 80 field table into smaller parts and have to write code to make sure each table gets a new record? Opinions please Bill |
#3
|
|||
|
|||
Access allows you to put over 250 fields into each record, and 80 is
nowhere close. Do you really need to put empty records into (for example) the [Billing] Table? One advantage to having separate Tables like yours is that empty records can be omitted, saving some space. I suggest leaving your Tables as they are and fixing your Queries. You can use OUTER JOIN clauses in your Queries to list everything, including customers with no billing information. In Query Design View, when you're linking the Tables, you do that by specifying that you want all the [Customers] records included, along with any [Billing] records that happen to match them. -- Vincent Johns Please feel free to quote anything I say here. KARL DEWEY wrote: All are linked by CustomerID and are 1:1 relationships There is no need to split anything that is 1:1. But I would think that you would have more than one order with a customer. Billing address can be in the customer table but the amount billed needs to be in the order or payment table. You do not need code when you split the table. Just use an append query to populate the tables. "Bill" wrote: In the middle of database design and need some advice - I am unsure if I went down the right path... In my DB, each customer has lots of information - fields. Each customer has contact info, Billing contact info, Device configuration info - about 80 fields in all I started by breaking these logical units into separate tables to keep each table manageable. I have a form based on a query that joins all the above back into a recordsource All are linked by CustomerID and are 1:1 relationships Problem is if fields are skipped during creating a new customer, and a table in the join does not get any field, then no record is created in that table. So, if I create a new customer and do not bother with any Billing info, then Billing table will not get a new record. I suppose, I could add code and checks to force data into each table before an update of a new customer record. But now I am wondering if I should just keep it simple and sweep everything back into one 80 field table. Is it worth the hassle to basically partition a 80 field table into smaller parts and have to write code to make sure each table gets a new record? Opinions please Bill |
#4
|
|||
|
|||
On Tue, 11 Oct 2005 19:58:07 GMT, "Bill" wrote:
In the middle of database design and need some advice - I am unsure if I went down the right path... In my DB, each customer has lots of information - fields. Each customer has contact info, Billing contact info, Device configuration info - about 80 fields in all I started by breaking these logical units into separate tables to keep each table manageable. I have a form based on a query that joins all the above back into a recordsource All are linked by CustomerID and are 1:1 relationships YOu need to split your table... but not that way. I don't know what "device configuration information" might be, but I'm guessing that in the real world customers are one type of entity, and devices are a different type of entity. Customers don't have "device configurations"; devices do! Does each customer have either zero or one devices, never more? Or do you in fact have a one (customer) to many (devices) relationship? Similarly for contact information: it looks like you have a one (customer) to many (contacts) relationship. In your example there are two contacts but might there not be more? perhaps a shipping contact? One to one relationships are very rare in practice: perhaps you could explain the nature of your data and these 80 fields a bit more. I strongly suspect you have at least these (and maybe more) one-to-many relationships hidden in each customer record, and that a Form with Subforms (which does *NOT* require that data be entered into every field!!!) will be a better design. John W. Vinson[MVP] |
#5
|
|||
|
|||
Thanks for the input.
Yes this is truly 1:1 data - customer has one device and one set of billing info. I guess my question comes down to: Should I break up a 80 field table just to make it smaller - ie no gains in efficiency of 1:many relational data? Bill |
#6
|
|||
|
|||
|
#7
|
|||
|
|||
|
#8
|
|||
|
|||
Appreciate the tips.
I am going to leave it as one table. Thanks Bill "John Vinson" wrote in message ... On Wed, 12 Oct 2005 00:12:33 GMT, wrote: Should I break up a 80 field table just to make it smaller - ie no gains in efficiency of 1:many relational data? A single table will be faster and more "efficient". You risk running into the somewhat obscure limitation that any single record may have no more than 2000 characters - if you have 50 of your 80 fields as 50 byte Text fields, you could hit 2500 bytes just by filling them all up. Access lets you CREATE this table, even use it for months, and then gives a somewhat cryptic error message if you actually use all that data! Beyond that - I'll just tip my hat to Vincent Johns, since John Vinson agrees fully and he said it better! John W. Vinson[MVP] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Multiple Options Group | Patty Stoddard | Using Forms | 19 | August 4th, 2005 02:30 PM |
Help with relationship plase | Rock | General Discussion | 5 | July 4th, 2005 03:54 AM |
Access combo box-show name, not ID, in table? | write on | New Users | 30 | April 30th, 2005 09:11 PM |
Seeking some expert advice. | HD87glide | Using Forms | 14 | March 23rd, 2005 10:11 PM |
Table Wizard Does Not Set Relationship if Foreign Key and Primary Key Name Do Not Match Exactly in Case. | HDW | Database Design | 3 | October 16th, 2004 03:42 AM |