Help designing SQL structure

[null-cipher]

Hi everybody.
I'm working on a little hobby project involving a MySQL database.
It's basically a second-hand parts inventory system (for a local computer store).
In my prototype my database is poorly designed, to put it nicely.

Each category of parts (hard-disks, RAM, CPUs, etc...) has a table of it's own.
Each table has these columns at the beginning:


+-------------+--------------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------------------+------+-----+-------------------+----------------+
| number | int(5) unsigned zerofill | NO | PRI | NULL | auto_increment |
| inward | timestamp | NO | | CURRENT_TIMESTAMP | |
| title | varchar(32) | NO | | NULL | |
| description | text | YES | | NULL | |
| source | varchar(64) | YES | | NULL | |
| cost | decimal(6,2) | NO | | 0.00 | |
| price | decimal(6,2) | NO | | 0.00 | |
+-------------+--------------------------+------+-----+-------------------+----------------+


Followed by the columns that are specific to each category. eg Harddisk has:
- Capacity (60gb)
- Size (3.5")
- Interface (SATA)

Or GPU would have:
- Brand (NVIDIA)
- RAM (64mb)
- Interface (AGP)

Note that some tables have more or less than 3 specific fields.
I've picked the brains of my school's sysadmin, and even he is scratching his head at this one.

Any help would be greatly appreciated!
(As an added incentive, if you figure it out you will be smarter than my school's sysadmin! :P)

[amac777]

I'm not sure exactly what you are asking so I'll assume you are just brainstorming possible ways how to organize spare computer parts in a database.

I think your idea of having separate tables for each type of part is a good approach. This lets you easily add specific fields that apply to those parts but not to other types. Also avoids lots of empty fields and it would be easy to see how many of a particular type of part you had on stock (by counting how many rows in that table).

So my main recommendation would be to have a top level parts index table that has the information you listed in the CODE box in your post. The reason is that this info is common to all parts. In this top level table you assign each part it's unique number. So no two parts would ever have the same number. Then, in all the sub tables, you have an column using the same parts number.

So something like this:

Part_index_table
- number (primary index, auto-increment)
- inward
- title
- description
- source
- cost
- price

harddisk_table
- number (primary index)
- Capacity (60gb)
- Size (3.5")
- Interface (SATA)

GPU_table
- number (primary index)
- Brand (NVIDIA)
- RAM (64mb)
- Interface (AGP)

Say you want to add a new hard drive part. You first create its entry in the parts index table. Let's say it becomes part number 23 (this was generated by the auto-increment). Then you add a row to the harddrive_table using the same number 23 to describe the specific details about this part.

Want to add a new type of part? Easy, just add a new table having the fields for that type of part.

[null-cipher]

Thanks for your reply, I had an idea that was similar to what you posted, I was just curious to see if it was a good idea or not.
I've had other reccomendations from people, and they were rather complex.

I'll give this a shot next time I have my db-machine handy.

Just as a heads up for me, what would an INSERT look like for this structure? (say, I wanted to add a 60gb Western Digital 2.5" harddisk to the db)

Thanks again. :)

[crush304]

another option

Part Table
---------
- number (primary index, auto-increment)
- inward
- title
- description
- source
- cost
- price
- part type id

Part Type Table
-------------
- id
- name (gpu, hardisk, etc)

Attribute Table
---------
-part number
-attribute name (capacity, brand, etc)
-attribute value (60 gb, NIVIDA, etc)

The benifit here is you can add part types with out having to update your db schema. if you really want you can also set up a table to track required attributes per part type