Fewer fields, more tables vs. fewer tables, more fields

[BobEulberg]

I'm trying to find the right balance of normalizing data. I know the basics like separating customer info into one table and the vehicles that belong to those customers in another.

But within that, should I have two vehicle-specific tables, one for VIN, license plate, etc, and another for driver name, address, etc? There would be the same record count either way, so nothing would be duplicated. I'm pretty sure queries that only need one of those tables would benefit from having less fields involved, but how badly would a query that needed both be hurt by having more tables?

The example of breaking 1 table into 2 might not make a big difference either way, but what about the scenario below?
Option A:
1 Table w/ 1 key and 100 data fields
Option B:
10 tables w/ each having the 1 key plus 10 data fields

Basically I'm just looking for a little guidance as to whether it's safer to err on the side of too many tables, or too few, and reasons why.

All help is appreciated!

 

[Dontremb]

The idea of breaking up data into different tables applies when you meet one of several conditions.

There's no reason to arbitrarily break up a table into several unless:

There's a one-to-many relationship between them:
IE: If several drivers utilize one vehicle, or if one driver uses several vehicles.

(This is probably not the case with you.)

If you have SO many data fields that you simply can't manage them all. But with Access 2007, there really isn't a problem with having a lot of fields in a table. Your queries and reports need only display what you want anyway.


Overall, I'd say keep to one table. If you split tables, and end up doing a one-to-one relationship between the two, you probably didn't need to split them.

 

[dhookom]

I don't know your data or business but I rarely believe a client when they state "There would be the same record count either way, so nothing would be duplicated". I also think "100 data fields" might be caused by un-normalized data.

I could be very wrong since you understand your specifications and I haven't seen them.

Duane
Hook'D on Access
MS Access MVP

 

[BobEulberg]

Thanks Dontremb. I'm only dealing with 20-30 fields at the most in this table, and there is a one-to-one relationship with the data. I guess I was just wondering what the magic number was regarding "too many" fields.

I've made a handful of databases before, and they all run into the same Access performance concerns due to numerous tables having hundreds of thousands of records. I'm just trying to do all I can from the beginning in this new project to keep performance as good as it can be.

Like my others, this will be a front-end/back-end system, with about 30 users. Previously I've always used one back-end database. I'll be nowhere near the 2GB max, but does anyone know if there is value in using multiple back-ends to split data?

 

[dhookom]

Since there are about 30 users, I would probably recommend breaking up the table into smaller ones. You state you have both driver and vehicle information. It might be possible that one user might be responsible for driver info and another for vehicle info. If this is the case, two tables would lessen the chances of one user locking a record needed by another user.

Duane
Hook'D on Access
MS Access MVP

 

[Dontremb]

dhookom makes a good point, Bob.

I hadn't considered the amount of users you would have, but consider it like this:

If all your fields are on one table, then if any of your 30 users happen to be using the DB at the same time, they will be locking the whole thing, whereas, as dhookom stated, if they're split into many one-to-one tables, there's less of a chance that two or more users will be utilizing the exact same table at the same time.

 

[dhookom]

Access doesn't generally lock tables. Either "pages" or records will be locked.

Duane
Hook'D on Access
MS Access MVP

 

[BobEulberg]

I generally get around record locks by having users edit data on forms tied to temporary tables, then update queries being run to change the live table when they 'Save' their changes. We haven't run into any problems with that approach.

The more normal the data, the better for storage purposes. But in terms of performance, if I can have a user hit one table instead of two, or run one query instead of two, that's the route I'd rather take.

Thanks for all the info!

 

[JoeAtWork]

should I have two vehicle-specific tables, one for VIN, license plate, etc, and another for driver name, address, etc? There would be the same record count either way, so nothing would be duplicated

I would say that Drivers and Vehicles would always be two tables. A vehicle could have multiple drivers, and/or a driver might use more than one vehicle. There are many people who own more than one vehicle.

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[PaulBricker]

I would agree with Joe and Duane. Each of your Drivers should have a primary key that uniquely identifies them. Each of your Vehicles should have a primary key that uniquely identifies them. You can't do that in a single table. If you did put all the info in one table, and for example a Driver left and you wanted to delete them from the table, you would also be deleting unique information about the vehicle. If a vehicle crashes and needs to be replaces, you can't delete information about that vehicle without deleting the driver as well. It would surely create problems down the road.

Paul

 

[dhookom]

Great points by Paul. Rather than deleting records, I generally prefer to set a status field to deleted or inactive or terminated. A driver should be able to have a different status from the vehicle.

Duane
Hook'D on Access
MS Access MVP

 

[BobEulberg]

All good points about separating drivers and vehicles. This database is actually a supplement to a larger full-fledged web app created by our full IT resources. I'm just the one-man show that gets to make these databases to fill the gaps. Drivers and vehicles are stored separately in that system.

In this database, my user only cares about the current assigned driver of a vehicle. Once a lease ends, my user doesn't care about the vehicle anymore. There's certainly reasons to split them, as has been pointed out, but for the specific business needs for this database they don't apply.

Thanks again everyone!

 

[MichaelRed]

Then, the RECOMENDED ovcerall processes of dealing with persistient data is (has been for at least a decade or two may be summed up as "Deep it better than wide", loosely translated ! "More tables / Records is better than more fields (and fewer records)"




MichaelRed

 

[BobEulberg]

I've never heard that expression before, "Deep is better than wide." Not to nit-pick, but I wonder what the cut-off is, or what the thought process is behind that expression.

For example, is it better to A) have 20 fully normalized tables with an average of 3 or so fields each and about 10,000 records, or is it better to B) have one giant table with 50 fields and 200,000 records.

Maybe it depends on the complexity of the query grabbing the data. If I was populating a form that needed every data field for one unit, I imagine it would be faster to run a query on scenario B). Simply selecting one record from one table where the key = x. A) would be so much more complicated.

However A) might be better if I was running some giant query with a lot of aggregate functions for reporting.

Thanks everyone!

 

[MichaelRed]

As with most / all generalizations, there must be exceptions. I do not recall the specific rationale for the concept, but i think part of it is the concept of avoiding the duplication of data. If it only resides in one location, it will not be duplicated (with the pseudo exception on Keys). Of course, this must include the concept of enforced referential integrity, however modern relational databases have this capability with the cascade updates for the rare occasion where a Key must be changed.




MichaelRed

 

[dhookom]

Rather than "Deep is better than wide" an Access MVP friend (John Vinson) states "Records are cheap, fields are expensive".

There are several levels of normalization. I certainly don't go as far as a few others but I try to follow most of the basics.

Duane
Hook'D on Access
MS Access MVP

 

[MichaelRed]

I never heard that specific expression, but is seems to be a similar concept.




MichaelRed