p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   BOOK: Professional CodeIgniter ISBN: 978-0-470-28245-8 (http://p2p.wrox.com/forumdisplay.php?f=414)
-   -   Poor db design? (http://p2p.wrox.com/showthread.php?t=75467)

patch July 29th, 2009 01:15 PM

Poor db design?
 
Anyone else think that having categories and subcategories in the same table is poor db design?

Surely categories is the 'parent' table and subcategories should be a 'child' table i.e. a one-to-many relationship!

apart from that ... so far, so good

Imar July 29th, 2009 05:16 PM

I don't know the book so I can't judge on the actual design, but it's not uncommon to store parents and children in the same table. Consider this table:

Code:


[Category]
Id        (int)
Name      (nvarchar(100)
ParentId  (int) (null)

Now you have a single table that allows you to store a tree of data:

Code:


1. Volvo      <null>
2. BMW        <null>
3. 850        1
4. 850 GLT    3
5. 850 T5    3

This is very useful when storing a tree with an unlimited depth. In this example, the 850 T5 is a sub category of the 850 series which in turn is in the Volvo category. To enforce the relationship, the table can have a self reference or self join where ParentId points to Id.

Not something you can easily accomplish with two separate tables.

Hope this helps,

Imar

patch July 29th, 2009 06:35 PM

Hi Imar,

Thanks for that explanation, I can see the power of the 'single' table. I simply thought that, for purity's sake, a simple parent-child may suffice. Point taken though.

and then of course you mentioned the volvo T5 ... the only car I ever owned where 2nd and 4th were completely redundant ...

Imar July 30th, 2009 02:16 AM

Hi patch,

Quote:

and then of course you mentioned the volvo T5 ... the only car I ever owned where 2nd and 4th were completely redundant ...
Hahaha, I know exactly what you mean..... (I own an old 850 T5)

Imar

myerman July 31st, 2009 12:08 AM

database design
 
I can see where a main category table and subcategories might be useful, if you consider that you might want to have multiple paths to a subcategory. For example:

Top > Mens Clothing > Blazers
Top > Monthly Specials > Blazers

Although, something like that might be better handled with non-hierarchical tags or keywords....also, I don't think something like this with separate tables would scale very well.....your thoughts?

patch July 31st, 2009 04:18 AM

Hi,

I suppose it depends on how you look at it. Why do you think it wouldn't scale very well?

Would you consider 'monthly special' to be categor, subcategory or simply an attribute of the product?

Cheers.

Enjoying the book so far, easily the best I've read on Codeigniter.

myerman July 31st, 2009 08:00 AM

scaling
 
Well, let me rephrase -- from a technical point of view, you'd scale okay. The database can handle it admirably. From a UX perspective, not so much. Users want to see a destination "stand still" if you will... if they got to blazers via men's clothing before, don't confuse them with some alternate route. You *might* get away with it if you only had two, but after that you're going to hit some problems.

It might also not scale from an editing/update perspective. Having a more complex set of categories that mix-n-match with subcategories might make it harder to determine, from a management perspective, which parts of your site need maintenance. For example, you might not intuitively know if it's time to take down subcategories under dresses because there's 27 of them. Or something. (Which might be counteracted by a policy that keeps the number down to some sane thing, which brings me back to, well, just have categories and subcategories in a single table.)

However, all that being said, yes, if I were to do some kind of "monthly special" or a "featured" item I'd probably add a field to a product, some kind of true/false switch, and then do something with that data--perhaps show it in a sidebar or on the home page or something like that. This approach gives me more flexibility.

patch July 31st, 2009 01:22 PM

Hi,

Thanks for taking the time to reply.

Horses for courses I spose and I do like the flexibility of the single table approach. However, I wasn't suggesting subcategories would be mixed and matched with categories, what I was suggesting was that subcategories were 'owned' (my old SYNON/2 days coming back) by categories and, in turn, products would be 'owned' by subcategories so:

Category: 01 Mens Clothing

Subcategory: 01 (from Category) 01 Shirts

Product: 01 (from cat) 01 (from subcat) 001 Blue Corn Crackin' shirt

That wouldn't provide any UX issues as far as I can see, but maybe I 've missed something. Probably less flexible that your solution but maybve more rigorous in design ...

Different views, different solutions - that's what keeps the world turnin' innit?

Once again, great book - only on page 89 so far but picked up so much from it.

cheers,
Patch.

myerman July 31st, 2009 09:30 PM

cool....
 
can you talk about your experiences with SYNON/2 more? These kinds of discussions are interesting, mostly because it allows developers of all stripes/kinds to talk about architectural concerns.

I like it a bit more straightforward, certainly, with more one-to-one relationship between my models and databases (unless I need some kind of mapping table between two tables, etc)....but what you're talking about might be more useful for other situations.

patch August 1st, 2009 06:37 AM

Sure,

I used SYNON/2 on IBM's AS/400 boxes for a good long while.

It is/was what they used to call a CASE tool. Put simply, it was a purely data driven approcah to developing applications on the above boxes. At it's heart was the 'datamodel'.

In the datamodel you described all your entities (tables), elements (fields) of those entities and attributes of each.

You also defiend the relationships between those entities by means of certain phrases.

For example, you may define a Category table with fields such as
CatId
CatName

Then, if you had subcategories that had to belong to to a category, you would simply say (via the datamodel)

Subcategory Owned by Category

that would simply add the Catid from Category into the definition of the Subcategory table, make it part of the primary key AND, crucially, that simple phrase would, when appropriate, generate the necessary code to check and enforce referential integrity ... it wouldn't be possible, through the application, to add a subcategory that didn't have a valid Category.

The other most commonly used 'phrase' was Refers To. So one table could refer to another for example Product could refer to Colour. So you have a Product table, full of products and a Colour table full of colours.

That Refers to phrase, in the datamodel, meant the the primary key for the colours table was added to the Products table but only as another field, not as a key - a foreign key if you will - AND, again it would generate the necessary code to enfor referential integrity ... you couldn't add a product with a colour that didn't exist in the Colours table.

The datmodel was pretty simple stuff, the real power was in the autogeneration of all the code - both programs and views. You only changed the bits you needed to ... it was kind of a theoretical OO approach to design and build.

Kind of like Rails or CI but much, much more in there. So much of the 'donkey' work was done for you. Prior to using SYNON/2 who ever had the time to write or test code to enforce db integrity? If the db didn't do it on it's own, it probably never got done!

Hope that makes sense and is what you were after.
Cheers.


All times are GMT -4. The time now is 07:44 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.