Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2013

    Database design help

    Hi total newbie here to basic database design. I'm looking for some one to explain to me how I would add foreign keys to the relevant entities to enable a relationship to be established between the following tables:

    I would also appreciate if someone could explain to me how I would determine referential integrity for each entity, and within the selection at least one occurance of cascade update and one occurance of cascade delete.

    These may be selected seperately, or together.

    I'm not asking any one to do this for me, just to explain how I would do it, so that I can learn. I have loads more after this I have to do once I've learned lol.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    try reading
    The Relational Data Model, Normalisation and effective Database Design
    Fundamentals of Relational Database Design --

    essentially you need to associate your entities when defining keys
    each and every table in a relational database MUST have a means of uniquely identifying a specific row within that table. a table may have one or more indexes, one or more of which can be unique (but having two unique indexes in a table ius relatively rare in the real world)

    so you need to look at how items in table X are related to table Y
    ferinstance a Vet probably needs to know who owns a pet (so it can rob the owner blind, sorry charge the owner for services)
    ferinstance a pet will usually have an owner

    on your current model you have a column dog/cat
    / is a reserved symbol in Access meaning it should not be used for table or column names

    there are other things to consider
    can there be more than one owner of the pet?

    can there be more than one vet in a vets practice?

    should there be more tables defining the pet?
    ferinstance should there be a pet type table (so its no longer dog/cat, but dog / cat / hamster....
    should there be an intermediate table between pet type and pet that identifies the type /breed of pet

    its never smart to use alpha columns such as a name as a foreign key.
    because some computer systems see John1302, john1302 or JOHN1302 as different spellings. there also used to be a concern about storage but that should be less of an issue these days
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts