rental contract database

[chermiti]

HI EVERYONE

this is my first topic here, anyway i'm not efficient in access, so i need help guys

i have an idea of a data base related to rental contract which includes this issue
1-customer
- Costumer name
- Type de revenue
- Branche
- ACTIVITE



2- contract

- Customer name
- Agreement
- Type agreement
- Manière facturation
- Location Code

- Date signature
- Date begining contrat
- Date Fin contrat

3- invoice
- Customer name
- Agreement
- Manière facturation (mensuelle, trimestrelle, annuelle)
- Location Code
- Descriptions
- Area (m²)
- Monthly price without vat
- Vat
- TTC

a customer may have many contrats with many location code
a customer may have many invoices

i have problems about choosing the primary key, and about the establishment of the relationships

thank u in advanace

[lfpm062010]

Base on what you have say.

The table "Customer" will have "CustomerNo" (create this field is recommended) or "CustomerName" as primary key.
Create a "Location" table with "LocationNo" as primary key.
The table "Contract" will have "CustomerNo" and "LocationNo" as its primary key (as long as there is no duplicate "CustomerNo" and "LocationNo" combination).
The table "Invoice" will have "InvoiceNo" (create this field is recommended) and "CustomerNo" and "InvoiceNo" as its primary (as long as there is no duplicate "CustomerNo" and InvoiceNo" combination).

Hope this helps.

[chermiti]

Base on what you have say.

The table "Customer" will have "CustomerNo" (create this field is recommended) or "CustomerName" as primary key.
Create a "Location" table with "LocationNo" as primary key.
The table "Contract" will have "CustomerNo" and "LocationNo" as its primary key (as long as there is no duplicate "CustomerNo" and "LocationNo" combination).
The table "Invoice" will have "InvoiceNo" (create this field is recommended) and "CustomerNo" and "InvoiceNo" as its primary (as long as there is no duplicate "CustomerNo" and InvoiceNo" combination).

Hope this helps.

for location do you mean location code as mentionned in my file, so you advised me to create another issue whose name location

[lfpm062010]

I recommended to create a "Location" table to store your location information and use "LocationNo" as its primary key. It is similar to your "Customer" table.

[chermiti]

i have resumed what you have say in below, please help me in order to define the relations between tables
please note

a customer may hane many agreements
an agreement may have many locations code

[lfpm062010]

Maybe something like this.

[chermiti]

EXCUSE ME but i didn' t understood the relations , first i don't need invoices table

- all description mentionned in the table invoices , i need them in table contract

[lfpm062010]

There are many way to do what you need. I gave you my opion on how to design your relationships based on your first post.

Good luck with your project.

[chermiti]

There are many way to do what you need. I gave you my opion on how to design your relationships based on your first post.

Good luck with your project.

thank u veru much

[ssanfu]

I would suggest using pencil and paper to design your tables and relationships..
You said "a customer may have many agreements".
Can an agreement (Contract) have many Customers?

If "an agreement may have many locations code", I would think a location can be in many agreements (contracts). This is a many to many relationship, so there needs to be a junction table.

If there is only one customer to an agreement (contract), I see 4 tables:
tblCustomers
tblContracts
tblLocations
tblContractsLocations (junction table)

I don't know what all of the fields are for, but I think they are in the correct tables with the exception of these fields:
"Type de revenue", "Branche" and "ACTIVITE".
I think they should be in the contracts table.
Attachment 20053

Attachment 20051

[chermiti]

thank you for the help

for an agreement, he's related to one customer

for the location code it's related also to only one agreement and one customer

i don't know if the relationships will change after this information

[ssanfu]

One agreement has one location and one location is in one agreement

You could have
Attachment 20061

or you add the fields in tbLocations to tblContracts.
I would leave it like the picture above. The location data should not have to be retyped again for next agreement.