Should I break up a big table or not?

[Bill]

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

 

[KARL DEWEY]

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.

 

[Vincent Johns]

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 <[email protected]>
Please feel free to quote anything I say here.

 

[John Vinson]

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]

 

[aa]

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

 

[Vincent Johns]

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

As I mentioned in another message, Access shouldn't have any trouble
with 80 fields, but the person(s) who have to maintain the database
might. I suggest carefully documenting what each field means, if you
haven't already. One thing I usually do is to put comments into the
Description column in Table Design View, including sample data values;
these Description fields show up in the status line at the bottom of the
screen when you select a field, to help remind you what should be in
there. You don't want the Field Name of the field to be long and
unwieldy, so you can use the Description to provide a bit more detail.

It still seems a bit strange to me that each customer has only one bill
-- don't you get any repeat business at all? But OK, under these
conditions, the Table should work just fine for you.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[John Vinson]

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]

 

[Bill]

Appreciate the tips.
I am going to leave it as one table.

Thanks

Bill