November 15, 200817 yr Hey all. I understand the basic principles of normalisation and the whole trade off balance but am struggling with a particular design issue. I'll explain the problem in the form of an e-commerce shop. I don't want the shop to have a forced amount of tiers in its product hierarchy. For example, Books would be: Books from... 2000's 2010 Dec 2010 Nov 2010 etc [*]2009 [*]etc [*]1990's [*]1980's [*]1970's [*]1960's [i won't write the full list but basically 4 tiers of hierarchy: e.g. Books > 2000's > 2010 > Dec 2010] But then CD's could be only 3 tiers. And T-shirts could be 10 tiers. Now under full normalisation that would mean doing a table for every value in ever tier (meaning potentially a hundred tables!). What would be the best way of keeping the scalability of the store but not having loads of tables?
November 15, 200817 yr OK, so I may have misunderstood what you're asking but I think you could do it in only 3 tables: Product Product_ID (primary key) Blah Blah .... Product_Categories_Link ID (Primary Key) Category_ID (Foreign Key) Product_ID (Foreign Key) Categories Category_ID (Primary Key) Category_Parent_ID Blah Blah .... Category_Parent_ID would have the ID for the category that comes before a Category_ID in the hierarchy or is null (or some default top-level category) if it's the top-level. Depends how complex you want to be with a recursive structure and that depends on what you need to do.
November 15, 200817 yr Author Cheers wizely, that's the sort of thing I mean. That would work. Would it look something like (with e.g.): Product Product_ID (primary key) - P000001 Product Name - Book 1 Date of Release - Dec 2010 Product_Categories_Link ID (Primary Key) - PCL000001 Category_ID (Foreign Key) - CT000300 <-- Being the last tier e.g. Dec 2010 Product_ID (Foreign Key) - P000001 Categories Category_ID (Primary Key) - CT000001 Category_Parent_ID - NULL Category Name - Books Category_ID (Primary Key) - CT000100 Category_Parent_ID - CT000001 Category Name - 2000's Category_ID (Primary Key) - CT000200 Category_Parent_ID - CT000100 Category Name - 2010 Category_ID (Primary Key) - CT000300 Category_Parent_ID - CT000200 Category Name - Dec 2010 I suppose an alternative to this could be for the Category_Parent_ID to have the value of just the top parent e.g. Books, and then the hierarchy is kept by the value of the Category_ID (e.g. Dec 2010 ID No (is greater than) 2010 ID No (is greater than) 2000's ID No). I think your method is more solid, but is that right in terms of putting it into practice? Note: Each top level product category could have between 4-8 tiers, but I'm trying to do it so that I could argue (I forgot to mention that this is for a Uni project) that it could be for a million tiers and that.
November 15, 200817 yr If all the tiers under the top-level categories (books, CDs etc) depend only on dates then you only need to store a date and just process it in the front-end to produce the millennia, decades and months. I suppose an alternative to this could be for the Category_Parent_ID to have the value of just the top parent e.g. Books, and then the hierarchy is kept by the value of the Category_ID (e.g. Dec 2010 ID No (is greater than) 2010 ID No (is greater than) 2000's ID No). I wouldn't do that! You should never rely on the value of a key - it would make changes to the hierarchy difficult and inflexible. Part of normalisation is ensuring keys are independent and random. Note: Each top level product category could have between 4-8 tiers, but I'm trying to do it so that I could argue (I forgot to mention that this is for a Uni project) that it could be for a million tiers and that. I've used a similar recursive structure in databases I've built with over 2 million records before.
November 15, 200817 yr Author If all the tiers under the top-level categories (books, CDs etc) depend only on dates then you only need to store a date and just process it in the front-end to produce the millennia, decades and months. Sorry, the dates thing was a bad example. It'll be just standard strings like Hats -> Big Hats -> Hats with ribbon -> etc. That's a good idea for the dates though to use in future. I wouldn't do that! You should never rely on the value of a key - it would make changes to the hierarchy difficult and inflexible. Part of normalisation is ensuring keys are independent and random. I had a feeling that wouldn't be good programming practice (well good practice at all it seems, ). Thanks for the input wizely, much apprieciated. I've been racking my brains for weeks on how to do this!
Create an account or sign in to comment