_Cars & _People linked somehow

[Sydcomebak]

I'm currently working in a gated community and I have a question about the structure of a database I want to build.

I want to create a database of cars, residents, guests, contractors, etc. The idea is to eliminate paper logging altogether.

I've played with MS Access in the past, but I had help. with that previous experience, I think that Access is designed to do what I want it to, but I wanted to make sure.

There are 121 homes in the community. Unfortunately, this is only half the issue. We are supposed to log every car that comes in. I figure a table of _Cars would look like this:

AAA111  Blue  Chevy  Blazer
BBB222  Grey  Dodge  Neon
CCC333  Gold  Acura  Legend
DDD444  Rose  Buick  Century
EEE555  Cyan  Honda  Civic

Anne Smith
Bill Jones
Mike Mason
Phil Welch
Dave Heath

What if Bill and Anne are married and they own The Blazer and the Neon,
but either one could be driving it on any day?

Essestially, the DB has to allow for a person to have unlimited cars and/or each car to be driven by unlimited people.

The goal is to set everything up by the plate #. The plate # can bring up an existing record of a person driving.

With a click of a command button, the entry is logged in a table with the current date and time, the vehicle info, and the person's info entering.

If anyone has any structural ideas, I'd love to hear them. I'm more than happy to pass a database back and forth across email if my description is not clear enough. Thanks for looking. I hope someone has the answers I need.

-Dave

 

[georgedwilkinson]

First table: Automobiles with ID as the autonumber primary key, color, license, make, model, etc.
Second table: Parties (people) with ID as the autonumber primary key, name, maybe apartment number (if you want to be lazy--otherwise you need a tenant table), etc.
Third table: PartyAutomobileTypes with ID as the autonumber primary key, AutomobileType, Description, To and From Dates, etc.
Fourth table: PartyAutomobiles with ID as the autonumber primary key, PartyID as FK, AutomobileID as FK, PartyAutomobileType as FK. UK on PartyID, AutomobileID, and PartyAutomobileType. Other fields as needed (Description, To and From Dates, etc.).

This is straight out of the book on normalization rules.

And you've still got a long way to go to make it work.

 

[Sydcomebak]

Thank you for the nod in the right direction. I'm looking into numerous articles on Normalization. I don't think i can really continue this project until structurally I have all of these parts worked out.

George, thanks again. You have given me a place to start from.

 

[georgedwilkinson]

Feel free to post back with additional questions.

 

[Sydcomebak]

After reading your suggestions, I went ahead and tried normalizing things. Do you think I'm on the right track?

-Dave

 

[georgedwilkinson]

I cannot really tell from this. Can you post the relationships? That is where the true design is shown.

 

[Sydcomebak]

I think this is what you meant. Let me know if you need any more.

-Dave

 

[georgedwilkinson]

It looks generally correct. Can't dissect it right now.

You might want to look at your relationship types. In most cases they should be 1:M.

Also, I don't think you need the _Plates table since that data isn't likely to be of value to you in the future...the data can easily be stored in the _Cars table, though I appreciate that a car is not defined by its plate and that a plate could go to another car. Usually, you'd only need that if you were writing a system for the DMV.

Not sure if "_Drivers" is the right name for that table (perhaps AutoDrivers, incorporating the "Auto" and the "Person" into the name). Also, "Person_ID" doesn't seem like the right name for a FK from _Drivers. Note, also, that you use People_ID in one place and Person_ID in another place. It should be consistent. Generally, the rule is to name tables the plural name and name fields the singular name.

This generally looks like it will work for what you said you were doing. I do wonder about the distinction between the owner of a vehicle and people who may be driving that vehicle. That might get you in a bind down the road...think about what's really going to happen as different drivers go through the gate.