normalization

[marha37]

Hi..I would love some help if anyone can answer..
I have a taxi leasing company and am having some trouble with normalization.

I have more than one corporation that has multiple medallions (taxi)
Each corporation has specific information for the corporation but, also
would be used for each medallion (taxi) in the corporation.
Each medallion is associated with one vehicle
Each vehicle can be driven by 1-3 drivers per week.
Each driver can only drive one vehicle at a time.
There are different payment choices depending on the taxi

I have built corp table, corp owners table, medallion table, drivers table,
payments table. Most of these tables the common primary key seems to be the
medallion number except the drivers which is thier Chauffeers license number.
I feel I should some how break this down further since all the primary keys
seem to be connected to the main element which is the medallion number. The
problem is that the primary key can not be duplicated and the number has to
be used again and again like in the payments. That is why I think I should
do something else.

I am hoping someone can help me to understand what i am doing wrong...or
right?
thanks for any help given!

 

[Jeff Boyce]

It sounds like you have multiple corporations. A table for corporations
would need to have an ID related to the topic of the table, that is,
corporations. If your corporations don't have a unique identifier already
(corpname is not a good candidate), consider using an Access Autonumber to
get a unique record/row identifier as a primary key.

You didn't describe this, but I'll guess that each corporation can be owned
by multiple owners (and that one owner may have a piece of ownership in more
than one corporation). To show this, you need an "owners" table and a
junction table between owners and corporations to hold the valid
combinations. The owners table would have an OwnerID, and, at a minimum,
the CorporationOwnership table would have OwnerID and CorporationID.

It sounds like one corporation can have many medallions ... are "medallions"
attached to a vehicle, or are the medallions the actual vehicle itself?
Yes, a MedallionID in a medallions table makes sense, but if one corporation
can own many medallions, then your Medallion table needs a field to hold
CorporationID, to show its corporation.

I can see having a drivers table (with DriverID), and including a DriverID
field in the Medallion table (to show who is driving that one). It sounds
like the DriverID can be their Chauffer's licence number.

I'm not understanding how "payment" relates to any of the above, so I don't
have any ideas on how to show that.

Is this what you were looking for?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[KARL DEWEY]

You did not say how the data was to be used.

Are you tracking who drove what, when, and something about money?

What kind of reports are you going to run? What would be a routine display?

Why do you need a corp table and corp owners table? Why not just list the
owners in the corporate table?