Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2011
    Posts
    5

    Unanswered: Need help in choosing a PK for a table

    Hi all,

    I am working on a project for a transport dept of a construction company. where i've to develop an information system for their fleet.
    Information like(Vehile/Plant,contracts, drivers/operators_logs,maintenance, repairs etc.)

    company has a large (mixed) fleet of vehicles equipment and miscellanuous items. (Generators/welding machines ,air compressors,crane Heavy/light vehicles)

    company has a numbering system to identify its fleet. Whole fleet is devided into Groups /Main Category . Sub Category/Serial Number. Thats how a plant is identified in records,

    e.g a toyota corolla will have its number as follows

    09/01.03/001H

    09 - is the group number (09 for light vehicles)
    01 - main category (Saloon Cars )
    03 - sub category (Corolla) if it is 4 then it means its a camry
    001h - the serial number with in this group,main cat and sub cat. (h for hired)

    for vehicles it is very easy to choose chassis # or engin # for a primary key but there are items which have no such things for example a turner shop is a whole container with leth machine and other stuff fixed inside, has to engin # and chassis number.

    then comes the registration # but again it fails becuase not all the items have registration number.

    That's the problem which brought me here to this fourm.

    Please advise which column shall i choose as a primary key??

    If i have to go for a composit primary key. like taking together (groupno,main cat, sub cat,serial) then how do i refer to this key in other tables??


    Your quick response will be highly appriciated.

    Please ignore any spell mistakes. If need further explanation please post a reply.

    Thanks a lot.

  2. #2
    Join Date
    May 2011
    Posts
    24
    how about using surrogate key (auto-increment)?

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why would you need a surrogate key if you have
    09 - is the group number (09 for light vehicles)
    01 - main category (Saloon Cars )
    03 - sub category (Corolla) if it is 4 then it means its a camry
    001h - the serial number with in this group,main cat and sub cat. (h for hired)
    unless its for performance, storage or other reasons

    the very fact that you have a proposed column called serial number surely must make it unique within that sub group. granted you could use a surrogate key AND define a unique index on the columns above to ensure that no one creates duplicate data.
    if you make a composite key then you 'merely' include those columns in any 'child' table.

    I guess it depends on the data you are trying to capture, but I woudl have though that each piece of equipment will have a unique (manufacturer's) serial number so a manufacurter code and serial number should suffice. but thats only iof each piece of equipment has a manufacturer's serial number
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    May 2011
    Posts
    24
    i probably understands it wrong but he is making a design for existing data & not all of them has the required info to form a unique/primary key

  5. #5
    Join Date
    Aug 2011
    Posts
    5
    Thank you reeson and Healdem first of all for replying. I am sorry for being late on reply.

    reeson is right that the system is about Existing Data so i've to stick to the company's plant identification/numbring system.

    Healdem point is valueable for me to use a menufecturer's serial number or engine number. It is ok/applicable to vehicles and other branded material, but there are things/material as i've mentioned earlier that,

    there are items which have no serial/engine number, e.g a turner shop is a whole container with leth machine and other stuff fixed inside
    I'd like to show you what i did so far and i'll appriciate your valueable comments. Follwing are the details.

    Groups Table
    Group_code PK
    Group_name

    Main_cats table
    Mcat_code PK
    Group_code PK (FK Groups too)
    Mcat_name

    Sub_cats table
    Plant_id Auto_increment PK
    Scat_code
    Mcat_code FK
    Group_code FK
    Scat_name
    Unique Index on bold ones.


    at this point the (09/01.03/) part of numbring system is ready and unique.

    now comes the plants table where i am using the Plant_id and Serial_no as composit where Plant_id comes from Sub_cats and pointing to a unique set of Group,Main_cat and sub_cat.

    It works but i am not sure whether it's alright or is it going to make problems on the long run and what effects it'll have on performance?

    Please let me know whether this approach is Good or is there any better way to accomplish this?

    At last please ignore any spell mistaks and anything unmannered.

    Regards,

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you have 'stuff' that is a combination of various items such as your containerised turner shop, then either give it an internal serial number, or choose say the conatiner serial number. usually an organisation has some form of unique naming / numbering system. in this instance it cold be 'turnershop', 'turnershop 5', or whatever. but somewhere there has tobe a mechanism by which the organisation "knows" which piece of equipment is referred to
    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
  •