primary/foreign keys

[st1300]

I was wondering if one can get away with having data types being different as my primary is autonumber and foreign is alphanumeric (text).



OR

Would I be better off using an autonumber as the foreign key as well?

Thanks

[pdebaets]

"Would I be better off using an autonumber as the foreign key as well?"

You should always strive to have an autonumber field be the primary key in each table.

The foreign key field should be data type number, long integer.

[st1300]

"Would I be better off using an autonumber as the foreign key as well?"

You should always strive to have an autonumber field be the primary key in each table.

The foreign key field should be data type number, long integer.

Thanks for reply...

A little explanation needed as to set the stage so to speak.

3 tables: customertbl, vehicletbl and servicestbl all of which have a primary key as autonumber.

If I'm understanding this correctly I would take the vehicletbl and servicestbl primary key fields and place them into the customertbl thus becoming the foreign keys. This would allow each customer to have many vehicles and services.

[pdebaets]

If I'm understanding this correctly I would take the vehicletbl and servicestbl primary key fields and place them into the customertbl thus becoming the foreign keys. This would allow each customer to have many vehicles and services.

Actually, this would allow each customer to have at most one vehicle and at most one service.

Also, services are for vehicles, are they not?

Here's a simple suggested table schema to get you started.

Table: Customers
PK: CustomerID (autonumber)
FirstName
LastName
etc...

Table: Vehicles
PK: VehicleID (autonumber)
Make
Model
VIN

Table: Services
PK: ServiceID (autonumber)
ServiceDescription

Table: CustomerVehicles (links customer with vehicles customer owns)
PK: CustomerVehicleID (autonumber)
FK: CustomerID (number, long integer)
FK: VehicleID (number, long integer)

Table: VehicleServices (links vehicles with services performed on vehicle)
PK: VehicleServiceID (autonumber)
FK: VehicleID (number, long integer)
FK: ServiceID (number, long integer)
ServiceDate

[st1300]

Actually, this would allow each customer to have at most one vehicle and at most one service.

Also, services are for vehicles, are they not?

Here's a simple suggested table schema to get you started.

Table: Customers
PK: CustomerID (autonumber)
FirstName
LastName
etc...

Table: Vehicles
PK: VehicleID (autonumber)
Make
Model
VIN

Table: Services
PK: ServiceID (autonumber)
ServiceDescription

Table: CustomerVehicles (links customer with vehicles customer owns)
PK: CustomerVehicleID (autonumber)
FK: CustomerID (number, long integer)
FK: VehicleID (number, long integer)

Table: VehicleServices (links vehicles with services performed on vehicle)
PK: VehicleServiceID (autonumber)
FK: VehicleID (number, long integer)
FK: ServiceID (number, long integer)
ServiceDate

Yes services are for each individual vehicle.

So, 5 Tables created and CustomerVehicles, VehicleServices set up with 3 fields as you have. Now set up relationships?

I thought I had abit more of a handle on this key thing, not so much yet...

[pdebaets]

If you have a split database, do this in your data back-end:

Open up relationships view and select "show all tables". In the Customers table, click and drag CustomerID and drop it on CustomerID in the CustomerVehicles table. That will make a line between the FK and the PK. Double click on that line and select "Enforce Referential Integrity". Do the same for the other PK/FK combinations.

[Rainlover]

Try this.

Table: Customers
PK: CustomerID (autonumber)
FirstName
LastName
etc...

Table: Vehicles
PK: VehicleID (autonumber)
FK: CustomerID
Make
Model
VIN

Table: Services
PK: ServiceID (autonumber)
FK: CustomerID
ServiceDescription

This should give you what I think you need. This is a One to Many Relationship.

[pdebaets]

Good point Rain. The Customers to Vehicles relationship is one to many, not many to many. I've updated my sample schema here:

Table: Customers
PK: CustomerID (autonumber)
FirstName
LastName
etc...

Table: Vehicles
PK: VehicleID (autonumber)
FK: CustomerID (number, long integer)
Make
Model
VIN

Table: Services
PK: ServiceID (autonumber)
ServiceDescription

Table: VehicleServices (links vehicles with services performed on vehicle)
PK: VehicleServiceID (autonumber)
FK: VehicleID (number, long integer)
FK: ServiceID (number, long integer)
ServiceDate

[st1300]

Hope this works? See attachment, you will need to zoom to see relationships.

This is my thought, but I'm not getting it.

One customer = possible more then one vehicle
one vehicle = many services done

[Rainlover]

Suggest you read up on Naming Conventions.

eg No Spaces
No /
No #

Primary Key should be the first just for easier reading.

Other that that what is your problem.

[pdebaets]

The PK's and FK's look OK :-)

I recommend that you don't use special characters (like "/", "#") in field names. Ex.: Instead of "Oil Filter #" use "Oil Filter Number" or "Oil Filter No"

The Services table should just hold information about the different services offered. "Completed Date" doesn't belong there. Put "Completed Date" on the VehicleServices table to indicate when a particular Vehicle had a particular Service completed.

[st1300]

Thanks guys for suggesetion thus far. I'm making the suggested changes as we speak.

RainLover, I've not yet laid down alot of info into the tables but it seemed to me with the on or two I did put in that I seen no way that the database knew what vehilce is whos and what services are for what vehicle. I'm going to add more data and see if I can see how it works.

pdebaets, I'll switch the date and see how it goes tonight with further developement time.

[st1300]

Good point Rain. The Customers to Vehicles relationship is one to many, not many to many. I've updated my sample schema here:

Table: Customers
PK: CustomerID (autonumber)
FirstName
LastName
etc...

Table: Vehicles
PK: VehicleID (autonumber)
FK: CustomerID (number, long integer)
Make
Model
VIN

Table: Services
PK: ServiceID (autonumber)
ServiceDescription

Table: VehicleServices (links vehicles with services performed on vehicle)
PK: VehicleServiceID (autonumber)
FK: VehicleID (number, long integer)
FK: ServiceID (number, long integer)
ServiceDate

Using this setup it appears the service table is a dead end as far as display the services preformed.

In table view the cust table (open the + sign) gives me the Vechicle table which gives me the VehicleService table and that is it.

I see the service table is one to many to the vehicle services table, should that not be the other way around? If so what keys??

Relationships and keys are not so easy

[st1300]

Looks like I now have what I think I should see. I now have three tables. Cust, Servies and Vehicle.

Cust Table: PK CustomerID

Vehicle Table: PK VehicleID
FK CustomerID

Services Table: PK ServiceID
FK VehicleID

Doing away with the VehicleServices Table (not sure why/what it actually did) and using above set up I now have one customer with many vehicles and many services for any one vehicle.

Unless I'm missing logic I've not yet seen? or come accross in the building foundation.

[Rainlover]

The Vehicle Services Table list the various Services that could be performed. It also holds information about the Service to be performed. Like Check Oil Lights etc

e.g. A Service, B Service,----------------------X Service

Then you simply select the service you want to perform.