[eluser]jedd[/eluser]
Fan ... rather than junkie..
I used to have a bunch of very bad habits - but people much smarter than me have (mostly) talked me out of them. One of the big mantras I get is 'aim for tall, thin tables - lots of them'. Which pretty much sums up the goal/effect of normalisation.
If I were to do this .. ?
Your and slowgarry's table structure is what I would lean towards. I'd have an
id column on
every table. I've been pondering how you'd try to force some sanity into the data on the way in - an attribute_attribute table, say - but can't come up with an elegant approach. I'd contemplate separating attributes into categories, specifically numeric versus everything else, just to make searches and calculations easier - but this could get messy. You'd need two columns in your attributes table to refer to the different attributes_items/values tables, for starters.
I like the idea of abstraction, but as I said earlier on, if you have a fixed number of
categories, and those categories are substantially different as far as attributes go, and that number of categories will
never change, and that number is small (anything up to 10, say) -- then I'd probably look at keeping them separate. What I'd watch out for, if you follow that design, is if you are ever tempted to make a 'misc' category .. then you've probably failed, as you now have a hybrid system, and you might as well have everything abstract (ie. everything is implicitly in a 'misc' category - as per the current discussion).
As for quelling your concerns on performance - really, put some test data together and crank up the CI Profiling library, and run some tests. We tend to underestimate the speed of databases, and consequently do some silly things with that expectation in mind.
Consider this forum page, as an example - and the number of DB queries required to generate it. 15 people per page - so that's 15 lookups to the user table (which is around 70,000 rows at the moment) to grab username, create_date, avatar and signature. It also grabs total-post count for each user - and if they've done it properly this is 15 x count() calls against the presumably quite massive message table (upwards of 110,000, judging by my current URL). There's a check/update in there to see/set if you've read the latest message. And that's just the body - the header has a few more DB calls of its own. Point being, this page loads pretty snappily, despite doing upwards of 35 db calls against two 70,000+ row tables.
