Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Professional CodeIgniter ISBN: 978-0-470-28245-8
This is the forum to discuss the Wrox book Professional CodeIgniter by Thomas Myer; ISBN: 9780470282458
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Professional CodeIgniter ISBN: 978-0-470-28245-8 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 29th, 2009, 01:15 PM
Authorized User
 
Join Date: Jul 2009
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
Reply With Quote
  #2 (permalink)  
Old July 29th, 2009, 05:16 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!

Last edited by Imar; July 29th, 2009 at 05:18 PM..
Reply With Quote
  #3 (permalink)  
Old July 29th, 2009, 06:35 PM
Authorized User
 
Join Date: Jul 2009
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 ...
Reply With Quote
  #4 (permalink)  
Old July 30th, 2009, 02:16 AM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
Reply With Quote
  #5 (permalink)  
Old July 31st, 2009, 12:08 AM
Wrox Author
 
Join Date: May 2008
Location: Austin, TX, USA.
Posts: 53
Thanks: 0
Thanked 5 Times in 5 Posts
Default 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?
__________________
Thomas Myer
Author, Professional CodeIgniter
http://www.tripledogs.com
Reply With Quote
  #6 (permalink)  
Old July 31st, 2009, 04:18 AM
Authorized User
 
Join Date: Jul 2009
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
  #7 (permalink)  
Old July 31st, 2009, 08:00 AM
Wrox Author
 
Join Date: May 2008
Location: Austin, TX, USA.
Posts: 53
Thanks: 0
Thanked 5 Times in 5 Posts
Default 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.
__________________
Thomas Myer
Author, Professional CodeIgniter
http://www.tripledogs.com
Reply With Quote
  #8 (permalink)  
Old July 31st, 2009, 01:22 PM
Authorized User
 
Join Date: Jul 2009
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
  #9 (permalink)  
Old July 31st, 2009, 09:30 PM
Wrox Author
 
Join Date: May 2008
Location: Austin, TX, USA.
Posts: 53
Thanks: 0
Thanked 5 Times in 5 Posts
Default 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.
__________________
Thomas Myer
Author, Professional CodeIgniter
http://www.tripledogs.com
Reply With Quote
  #10 (permalink)  
Old August 1st, 2009, 06:37 AM
Authorized User
 
Join Date: Jul 2009
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQLDataReader - Extremely poor performance Miraval ADO.NET 1 October 2nd, 2007 12:12 PM
Working with poor named Access procedures seananderson Classic ASP Professional 3 December 24th, 2006 12:42 PM
Schedule DB design sammy1971 SQL Server 2000 0 April 30th, 2006 12:51 AM
DB Design Recommendations Needed ~Bean~ SQL Server 2000 1 July 14th, 2005 03:49 PM
need help with this db design method Access 4 May 11th, 2005 07:16 AM



All times are GMT -4. The time now is 10:52 PM.


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