null-cipher
April 30th, 2009, 10:18 AM
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)
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)