Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jan 2009
    Posts
    15

    Computer Parts Database Design

    Hello database gurus!

    I have been trying to figure out a database design for my projects for a week already and I have nothing. I want it to work and work efficiently. So here is the overview:

    I have a database that stores information on computer parts and I want the tables to store each part's specifications (memory, interface, speed, etc.). Now since each part have different specifications, it obviously would not make any sense to have one table to store all the parts. So I decided to use sub-tables and each part would be a table (Table_RAM, Table_VideoCards, Table_Harddrive, etc.) and have a parent table that has the universal information (model#, name, etc.).

    Problem is searching, I can't figure out how to index the tables and search the tables efficiently. How would I search all the tables and not create a huge SQL query consisting of many joins or unions.
    I considered using views and then using fulltext indexes but I'm not sure if that is the best way to go. Any other ideas would be greatly appreciated!

    Thanks,
    dyip

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dyip
    How would I search all the tables and not create a huge SQL query consisting of many joins or unions.
    huge? how is one join per table huge?

    obviously if your sub-tables have all the searchable columns, this is exactly what you have to do
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2009
    Posts
    15
    Well lets say I have about 9 subtables, then wouldn't I have to join all those subtables in a query if I wanted to search across the entire database with one key?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    let me ask you about these sub-tables... what are they a "sub" of?

    is there a main table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by dyip
    I have a database that stores information on computer parts and I want the tables to store each part's specifications (memory, interface, speed, etc.). Now since each part have different specifications, it obviously would not make any sense to have one table to store all the parts. So I decided to use sub-tables and each part would be a table (Table_RAM, Table_VideoCards, Table_Harddrive, etc.) and have a parent table that has the universal information (model#, name, etc.).
    PC technology is a rapidly changing field. How will you cope with these changes? By adding new tables each time and making all the associated changes to existing SQL? Or would you just limit what items you store to just the tables you have available so if you didn't have a CPU coolers table then you just wouldn't store this information or sell those products. Same thing goes for network cards, card readers, NAS devices etc etc.

    Of course even if you have a table for a given type of hardware then you'll still find that the technology (and the fields needed to describe this technology) will change rapidly over time. I think you'll either have to cope with a maintenance nightmare or learn to live with a database that's quickly going to become quickly out of date.

    Quote Originally Posted by r937
    Quote Originally Posted by dyip
    How would I search all the tables and not create a huge SQL query consisting of many joins or unions.
    huge? how is one join per table huge?
    It's a huge piece of SQL because when the user enters a search string you then have to match it against every field in every table (Table_VideoCards, Table_Harddrive ...) to see if you can find any matches. Apart from it being a nasty lump of SQL it would also be very inefficient.

    Of course you could force the user to pick which table they want to search from but that isn't regarded as being very user friendly these days. Even then it would still result in a table scan as you'd have to search every field in every record for matches.

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by dyip
    Well lets say I have about 9 subtables, then wouldn't I have to join all those subtables in a query if I wanted to search across the entire database with one key?
    Not necessarily, if you ensure that the attributes common to all sub-types are in the parent table rather than in any sub table.

    As a rule when you have a hierarchy of sub-type tables, attributes common to more than one sub-type should go in a table as close as possible to the root of the tree.

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by dportas
    Quote Originally Posted by dyip
    Well lets say I have about 9 subtables, then wouldn't I have to join all those subtables in a query if I wanted to search across the entire database with one key?
    Not necessarily, if you ensure that the attributes common to all sub-types are in the parent table rather than in any sub table.
    It would obviously be easier and faster to search on a common field in the parent table but that just means you'd only be able to search on name and have to ignore all the fields in the sub-tables. If you wanted to still search on these fields then you'd still need a large chunk of SQL to perform the search.

    I guess you could have a two level search where the first search just searches the name field in the parent table while the deeper (and much slower) search would check each field in the sub-tables.

    Another method might be to only allow searching on the name field but then also provide hierarchies within each sub-table so a user could find what they're after ie HDD > internal > SATA > 1TB > Seagate. These hierarchies could be built up automatically and perhaps include ranges for things like prices.

  8. #8
    Join Date
    Jan 2009
    Posts
    15
    Quote Originally Posted by mike_bike_kite

    Another method might be to only allow searching on the name field but then also provide hierarchies within each sub-table so a user could find what they're after ie HDD > internal > SATA > 1TB > Seagate. These hierarchies could be built up automatically and perhaps include ranges for things like prices.
    I considered that at one point but it still wouldn't be as user-friendly as I want. And I'm not too crazy about the idea of having 2 queries. How do sites like newegg accomplish their searches across their broad categories? I do not mean to make a database like theirs but it would be nice to have a starting point for this project.

    Quote Originally Posted by r937
    let me ask you about these sub-tables... what are they a "sub" of?

    is there a main table?
    Yes, there is a main table that consists of all the part's common information. e.g. name, model#, manufacturer, etc.

    Quote Originally Posted by mike_bike_kite

    PC technology is a rapidly changing field. How will you cope with these changes? By adding new tables each time and making all the associated changes to existing SQL? Or would you just limit what items you store to just the tables you have available so if you didn't have a CPU coolers table then you just wouldn't store this information or sell those products. Same thing goes for network cards, card readers, NAS devices etc etc.

    Of course even if you have a table for a given type of hardware then you'll still find that the technology (and the fields needed to describe this technology) will change rapidly over time. I think you'll either have to cope with a maintenance nightmare or learn to live with a database that's quickly going to become quickly out of date.
    It would be nice to have a dynamic database design (somehow) that can keep up with the changes but that may be a little too far ahead right now. I am ready to scrap my subtables appoach and start a brand new one if we can manage to come up with something better. But right now, the only thing that I can think of to make a database-wide search is to create one select query per subtable. This would be painfully slow and as you said, a nightmare to update and maintain.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    assuming we are not dealing with CLOB datatypes being searched, here is but one idea off the top of my head

    in the main table, add a VARCHAR(n) column, where n is sufficiently large

    when inserting a new main table row, place in this column the concatenated column values from whichever subtable row this main row represents

    then search that one concatenated column

    to avoid the "huge" join right away, you might return your result set of just main table rows, and then obtain the appropriate related subtable rows as a second step

    do not be too quick to abandon the supertable/subtable model just to get to the coding
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2009
    Posts
    15
    Hmm...i see what you're saying but wouldn't doing that create 2 sets of identical data? And so my database wouldn't be normalized...

    And I've been stuck with this problem for a good while, desperate situations call for desperate measures

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, it would still be normalized

    (normailzation does not mean that there is no redundancy -- it has to do with whether you can unambiguously return a specific attribute given the value of a primary key)

    and it wouldn't be identical -- in the subtables you would have proper datatypes in proper columns, but in the search string on the main table you just have a string of words which came from all the subtable columns strung together, and this is quite sufficient for the type of LIKE searches you're planning to do
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2009
    Posts
    15
    I see...it does sound like it will work in the short run but wouldn't it pose problems in the long run? When my database gets bigger and thus the concatenations would be pretty big too...maybe i'm just being paranoid?

  13. #13
    Join Date
    Jan 2009
    Posts
    15
    Hey r937, thanks for all your help so far.

    I decided to try your way of concatenating the columns and the search works (surprise!) but it only returns the main table results. If i wanted to return the subtables data (so it's like an overview) too, how would i be able to do that? I'm guessing multiple queries, one per table?

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not multiple queries necessarily -- a single query with multiple LEFT OUTER JOINs will suffice
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by dyip
    Quote Originally Posted by mike_bike_kite
    Another method might be to only allow searching on the name field but then also provide hierarchies within each sub-table so a user could find what they're after ie HDD > internal > SATA > 1TB > Seagate. These hierarchies could be built up automatically and perhaps include ranges for things like prices.
    I considered that at one point but it still wouldn't be as user-friendly as I want
    It would be worth going to a computer supplier like dabs and trying it out. Look at the refinements available when searching for a hard drive - I can see all the options and quickly find the ideal part that suits my needs. If I know what I'm searching for then I just use a search tool to find the cheapest supplier of that part - it wouldn't really matter what facilities your database offered it the price wasn't cheaper than the other suppliers. That means you have to supply useful information to the user otherwise your efforts will be ignored.

    Another feature you could consider is linking compatible parts together ie what CPU's, RAM can be used with what motherboards etc.

    Quote Originally Posted by r937
    actually, it would still be normalized
    What you're doing makes sense in that it allows you to search all the data with a single piece of SQL but I think you're pushing it to call it normalized. It's no different to caching monthly totals of data to make reports quicker. You can have triggers galore to sync up the data but you'd never call the cached data (the monthly totals or these long strings) normalized.

Posting Permissions

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