I don't see that two tables are going to be a truly viable solution. Your items have sufficiently unique and non-overlapping attributes that trying to stuff them all into one huge table is going to wind up with many items having a lot of blank fields. You're just begging for trouble with that. To distinguish what items have what attributes, you'd either have to create an "itemAttribute" table, describing what items in what categories have what attributes (and that's going to complicate your SQL), or you'll need to move that information into your code which will then require code updates to be more frequent as you're forced to synchronize the code and the database with subsequent updates.

I'd probably start by building a table similar to the following:

nodeID  parentNodeID  Description
------  ------------  -----------
1       1             persons
2       1             kids
3       1             grown_ups
4       4             books
5       4             manuals
6       4             dictionaries
[download]

You can make the nodeID the primary key and have a foreign key constraint from the parentNodeID to the nodeID. To find all root nodes:

SELECT nodeID where nodeID = parentNodeID
[download]

That's fairly easy to drill down in and with proper indexing, should be simple to search. It gets a little more complicated with items because you can't just stick a nodeID in items and then select all items with the appropriate nodeID. Perhaps you could create a meta-table listing the table names of the items you need and create lookup tables with that (mind you, this is all off the top of my head).

Obviously, you want this to be as fast as possible, but I would strongly caution you against designing this system with speed in mind. You want your database schema to be correct. If the schema is poor, you're much more likely to suffer corruption from anomalies. If that starts to happen, you either have to redesign your database correctly, or put in a bunch of extra code to prevent the anomalies. The latter option, of course, is going to have a performance impact, so you'd wind up losing what you might think you gain by trying to optimize up front.

Prematurely optimizing is a common problem. Anyone with any experience with databases knows the value of a properly normalized schema. Since your application has not been written yet, you can't judge whether or not performance is going to be a problem. Why trade the known benefits of normalization with the unknown benefits of optimization when you don't even know if performance is yet an issue?

Cheers,
Ovid

Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.