Page 1 of 3 123 LastLast
Results 1 to 15 of 39
  1. #1
    st1300 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    24

    primary/foreign keys

    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

  2. #2
    pdebaets is offline Competent Performer
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Los Angeles
    Posts
    235
    "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.
    Last edited by pdebaets; 11-27-2011 at 10:22 AM. Reason: more specific

  3. #3
    st1300 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    24

    Question

    Quote Originally Posted by pdebaets View Post
    "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.

  4. #4
    pdebaets is offline Competent Performer
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Los Angeles
    Posts
    235
    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

  5. #5
    st1300 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    24
    Quote Originally Posted by pdebaets View Post
    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...

  6. #6
    pdebaets is offline Competent Performer
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Los Angeles
    Posts
    235
    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.

  7. #7
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    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.

  8. #8
    pdebaets is offline Competent Performer
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Los Angeles
    Posts
    235
    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

  9. #9
    st1300 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    24
    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

  10. #10
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    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.

  11. #11
    pdebaets is offline Competent Performer
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Los Angeles
    Posts
    235
    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.

  12. #12
    st1300 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    24

    update

    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.

  13. #13
    st1300 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    24
    Quote Originally Posted by pdebaets View Post
    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

  14. #14
    st1300 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    24
    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.

  15. #15
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    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.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need help with foreign keys
    By acdougla17 in forum Access
    Replies: 1
    Last Post: 09-26-2011, 03:50 PM
  2. Design dilemma; multiple tables with same foreign keys?
    By squirrly in forum Database Design
    Replies: 9
    Last Post: 08-16-2011, 10:43 AM
  3. Foreign keys in a consolidated table
    By threepwoodjr in forum Database Design
    Replies: 3
    Last Post: 01-14-2011, 11:25 PM
  4. import the foreign key into the primary key
    By vCallNSPF in forum Forms
    Replies: 3
    Last Post: 01-14-2010, 06:51 PM
  5. Primary/foreign keys + relationships
    By tripptofer in forum Database Design
    Replies: 0
    Last Post: 08-12-2009, 07:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums