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.

how about using surrogate key (auto-increment)?

why would you need a surrogate key if you have
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 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

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,

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,

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