Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2008
    Posts
    13

    Unanswered: Creating primary key and relations

    Hello

    I am qite new to dbase and cannot get clear with creatin relation

    I would have table with presonal information: Name, Surname, Address, Birthdate and MI-ID - index number which is unique for each person. This number is asigned in some urelated base, but I would like to use it in my base as well.
    This means that I would type MI-ID manualy, same values shouldnt be allowed and I think this should be primary key.

    In separate table/s i would have
    table1 : event: IDevent, reson, observation, date
    table2: reclamation: IDreclamation, problem, date


    Each person will have at least one event, some of them two or more. - Should I create one to many relation between MI-ID and IDevent? for easier review of tables I would like to have MI-ID number from personal data in table1 as well, which means that in table1 there could be several entries with same MI-ID.

    for table2 there would be only rarely any entry. Besides conection with personal data it would be related to events.
    I mean if one person has three events, and reclamation as well I would like to know to which event reclamation is related.

    Best regards, Klemen

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    a primary key is 'just' a unique key (ie no duplicates allowed), which uniquely identifies the row in the table, and is used as a foreign key in 'child' tables, aside form that its no different to any other unique key.

    you can have multiple indexes, which can be unique, or allow duplicates
    you can have more than one unique key in a table
    any key can be comprised of one or more columns, called a composite key.

    quite often priamry keys are auto-generated (meaning the computer creates the key itself without reference to the user.. in this case the key should not be coerced to have meaning outside the system. this often causes problems where system designers try to make an auto-generated key an invoice number or other ascending numeric sequence.. this temptation should be resisted as it will cause problems over time

    however not all primary keys sould be auto-generated, if you can find an alternative from within the data itself use it.. these are so called 'natural keys', and may be drawn from several 'candidate keys'.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by krogelj
    This means that I would type MI-ID manualy, same values shouldnt be allowed and I think this should be primary key.
    correct, this can be your primary key


    Quote Originally Posted by krogelj
    Should I create one to many relation between MI-ID and IDevent?
    yes, the MI_ID should be included in table 1, where it will be a foreign key back to the personal table


    Quote Originally Posted by krogelj
    for table2 there would be only rarely any entry. Besides conection with personal data it would be related to events.
    I mean if one person has three events, and reclamation as well I would like to know to which event reclamation is related.
    in that case, the IDevent should be in table 2 as a foreign key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Aug 2008
    Posts
    13
    Thanks both of you for your help.

    One think that I dont know is the foreign key. How to create one?
    Should I just connect fields in relations and make one to many relation between MI-ID in personal data table and MI-ID field in event table?


    For table2 of reclamation I would like conection as you mentioned between IDevent and IDreclamation, but I would also like to have MI-ID value in table.

    One more question- if I create a database to work with it on desktop computer - is it possible later to convert it on server so that data can be input from other computers as well? Or is it better to put it directly on server.


    Best regards.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    [QUOTE=krogelj]...One think that I dont know is the foreign key. How to create one?
    Should I just connect fields in relations and make one to many relation between MI-ID in personal data table and MI-ID field in event table?/QUOTE]

    In Access a relationship is defined in the Relationships screen (Tools | Relationship) select the table that is the parent (has the primary key) and drag across to the table that has the 'child' (foreign key). It may be a little different from that.. its a while since I've used Access to design tables

    Quote Originally Posted by krogelj
    ...One more question- if I create a database to work with it on desktop computer - is it possible later to convert it on server so that data can be input from other computers as well? Or is it better to put it directly on server.
    Access can happily talk to server databases or its default fileserver system JET. JET will happily support up to somewhere around 15..30 users, above that you need to do some clever design work. You certainly shouldn't see any performance problems with say 5..10 users
    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
  •