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

    Unanswered: Having trouble with creating relationships!


    The task is:
    to set up a database for a charity who mainly raise money by selling tickets to their annual ball and selling products at an auction which takes place at the annual ball....oh and donors can either be companies or private individuals - so I have a table for company donors (info on the company), company contacts (as you might have a number of contacts for each company) and individual donors.

    The tables I have set up: (primary keys in red)

    Employees (who sell the tickets)
    Employee IDEmployee First Name
    Employee Last Name

    Company contacts
    Company Contact ID
    Company Donor IDContact First Name
    Contact Last Name

    Company donors
    Company Donor IDCompany Name

    Individual donors
    Individual donor IDFirst Name
    Last Name

    At this point, the relationship between company donors and company contacts would be one-to-many right? Where one company may have many contacts, but each contact can belong to only one company.

    The problem I'm having though is how do I make sure Individual Donors and Company Donors have unique IDs? Since I have them listed in 2 seperate tables, they'll start numbering from 1 onwards, so there would be an individual and company donor for every number which isn't what I want - so how do I get around that?

    Ticket Type
    Ticket Type ID

    Ticket Sales
    Ticket Type ID
    Employee ID
    Donor ID (need to show which individual or company purchased it so both types of donors need a unique ID - as explained above)

    In ticket sales, what should I have as my primary key?

    Auction products
    Product ID
    Starting Bid

    Auction Sales
    Transaction ID
    Donor ID (again I need to show which individual or company purchased it so both types of donors need a unique ID)
    Final Bid

    I could really do with some help figuring out the relationships! By the way I'm not sure if I've chosen the right field names as the primary keys so let me kow please if you spoy any that are wrong.

    Thank you for all your help in advance!!

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    keys are formed out of indexes
    keys can be one of
    Primary Key (PK):- the unique index that is used fore referential integirty. it uniquely identifies a row
    Foreign KEY (FK):- an index in one table that can have multiple identical values. its a foregin key when referring to another column in a table when in that other table the value is unique and a PK
    You can have as many unique indexes as required, but only one PK
    you can have multiple FK in a table, but each FK points to one other table, a FK cannot point more than one table
    a candidate key is a term used during the modelling phase to indicate that this could be a PK, hence a candidate

    you can have natural keys (where the data has an obvious unique value. at first glance you might consider a vehicle registration number as a natural key, but depending on where you live it may not be appropriate as the number plate may change or be moved to another vehicle, so in a strict modelling sense a better natural key would be the VIN which is supposed to be a unique number worldwide (unless you have a vehicle that has been been stolen or a valuable antique vehicle which has been restored, one car went into the shop two or more came out of the shop)

    if you are unclear then do some reading, often these are cited as good primers:-
    Fundamentals of Relational Database Design --
    The Relational Data Model, Normalisation and effective Database Design

    sometimes when doing the design you need to contemplate what is called the sub/supertype
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2013
    Hi, thanks for your reply, I'm reading through your links and trying to figure out a sensible way to approach the task - slowly but surely I'll get there hopefully (first time I'm using Access so its taking some time playing around with it to figure out how to show certain things) - but anyway thanks for your replies.

    I think I may be complicating the indvidual donor/company donor/company contact bit - is there a way to to start of with firlds such as first name, last name etc and then a field to allow input of a company name (in the case of company contacts) and if the user does, they are directed to another orm to input data about th company details, and if they don't, they carry on completing the form as an individual donor?

    Thank you.

Posting Permissions

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