Results 1 to 4 of 4

Thread: Help designing SQL structure

  1. #1
    Join Date
    Dec 2007
    Location
    New Zealand
    Beans
    64
    Distro
    Kubuntu 7.10 Gutsy Gibbon

    Question Help designing SQL structure

    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:
    Code:
    +-------------+--------------------------+------+-----+-------------------+----------------+
    | 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! )
    ~Peace Out!

  2. #2
    Join Date
    Aug 2006
    Location
    Canada
    Beans
    389
    Distro
    Ubuntu 10.04 Lucid Lynx

    Re: Help designing SQL structure

    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.

  3. #3
    Join Date
    Dec 2007
    Location
    New Zealand
    Beans
    64
    Distro
    Kubuntu 7.10 Gutsy Gibbon

    Re: Help designing SQL structure

    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.
    Last edited by null-cipher; May 1st, 2009 at 12:50 AM.
    ~Peace Out!

  4. #4
    Join Date
    Jul 2007
    Beans
    66

    Re: Help designing SQL structure

    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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •