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

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

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?

let me ask you about these sub-tables... what are they a "sub" of?

is there a main table?

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'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.

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.

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.

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.

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

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.

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

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

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

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?

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?

not multiple queries necessarily -- a single query with multiple LEFT OUTER JOINs will suffice

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.

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.