Advice for a equipment specification database

[Maracles]

I am currently planning a database that will store various types of camera equipment and a list of their specifications, however I am unsure the of the best way.

 

Although the categories are not final I will have broad categories such as:

 

Camera

Lens

Accessories

 

I am proposing a new table for each category so:

 

Table -> Cameras

Table -> Lenses

 

However the items contained within each table will have hugely varying specifications for example I may have film AND digital cameras; each with unique specifications i.e. (film format vs. digital format). Is it better to have a huge number of columns for all potential specifications within this one table and then simply leave them blank for items they do not apply to? Or is there a better way of doing it i.e. have a table only for specifications and then linking this to the actual items which will be stored in other tables?

 

 

Thanks in advance for the help.

 

 

 

 

 

 

[the182guy]

I would steer clear of creating a new table for every type of equipment here because in future you don't want to have to keep creating new tables when you want to add another type of equipment.

 

I would do it like this

 

type_attributes
---------------------
id
label (e.g. Zoom Amount, Size, weight, batterylife)
type_id (this is linked to the types table)


type_attributes_assoc
-----------------------------
id
attribute_id (this is linked to the type_attributes table)
value
equipment_id


equipment
---------------
id
type_id (linked to types table)
price <- field that applied to all equipment
addeddate <- field that applied to all equipment


types
---------
id
name (e.g camera, lens, tripod)

 

The advantage of this way is to add a new equipment type you just insert into the types table, and add the attributes for that type of equipmrnt in the attributes table. This structure is good if each piece of equipment is only ever one type.

[Maracles]

That was very helpful indeed, that massively condenses the number of tables I would have had!  Thanks a lot. Have started a new topic based on the same database, if you have time your thoughts on that would also be very useful. Cheers.