Confused with database relationship...

[cysklement]

Hi,

I am creating a machine leasing and complaint database.



I am confused whether a machine shall have a one to one relationship to a customer
or
a machine shall have one to many relationship.... Is there a database sample that I could look into..


For instance, if a customer complain about a machine and let us know about the machine tag...we will able to find the customer info(search).....by keyin the machine tag who is currently leasing the machine. Then, when the machine is returned and leased by company B. Then he enter the machine tag again in the search box it will only show the details of the company B.. But then it will also keeps records of the previous company which has leased the machine before.....

[June7]

Each machine can have many customers. Each customer can have many machines. That is many to many relationship. Requires a junction table.

[orange]

Determining relationships is done by examing (and testing) your business rules.

As June pointed out

A Machine may be leased to many Customers
A Customer may lease 1 or Many Machines

So your tables, initially, would be

Customers
Machines
and a junction jcnCustomerLeasedMachine

Customers------->jcnCustomerLeasedMachine<-------Machines

Now, Complaints-- are all complaints about leased Machines? Could a Complaint be for another reason/topic?
Do you want to know if a Complaint was resolved? Or had action taken?

[June7]

Maybe the MS Customer Service template will be helpful http://office.microsoft.com/en-us/te...010206880.aspx

[Jim Connell]

The other responders are correct. You've hit a critical concept in building robust databases - table and relation structure.

If you want a detailed approach to the problem you can't go wrong with Database Design for Mere Mortals by Hernandez.

If you want a quick overview take a look at the Access Wizard Page

http://custom-software.biz/accessWizard.html

and do search on "normalization" and "relation".

Hope this helps.
Jim Connell
Custom Software
978-392-3462
http://custom-software.biz
Home of the Access Wizard