Jump to content
Larry Ullman's Book Forums

Extending The Database Model


Recommended Posts

hi

 

Interesting book especially the stored procedures. I'd be interested to hear from anyone who is interested in extending or generalising the database; thus creating more variables to search upon. After all, not all of us want to sell coffee do we? I'm especially interested in filters or searching by specific categories see:

 

http://acookson.org/wp-content/uploads/2013/01/shop-screenshot.png

 

for an example of what's on my mind.

 

In my fictitional jeans shop example, I was playing around with MySQL workbench to come up with some possibilities for an ER diagram:

 

http://acookson.org/wp-content/uploads/2013/01/ecommerce3.png

 

It's totally at prototype stage and was looking for some inspiration really; maybe an on-going discussion around this topic.

 

best wishes

 

Andrew

Link to comment
Share on other sites

The examples should be perfectly trasferable to other type of products too.

Indeed. And I acknowlege that. That's the idea of open source you share ideas collaboratively.

 

Read the parts you did not fully get another time,

 

I didn't say I'm struggling to understand or didn't get something. Although examples in the book are pretty rigorous from the start they're well taught by the author. That's not to say they couldn't become more difficult pretty quickly. For instance, in my denim jeanshop example, my revised products table may look like:

 

 

-- -----------------------------------------------------
-- Table `default_schema`.`products`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `default_schema`.`products` (
 `id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT ,
 `category_id` TINYINT(3) UNSIGNED NOT NULL ,
 `size_id` TINYINT(3) UNSIGNED NOT NULL ,
 `colour_id` TINYINT(3) UNSIGNED NOT NULL ,
 `designer_id` TINYINT(3) UNSIGNED NOT NULL ,
 `price_id` TINYINT(3) UNSIGNED NOT NULL ,
 `name` VARCHAR(60) NOT NULL ,
 `description` TINYTEXT NULL DEFAULT NULL ,
 `image` VARCHAR(45) NOT NULL ,
 `price` DECIMAL(5,2) UNSIGNED NOT NULL ,
 `stock` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0' ,
 `date_created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
 `categories_id` TINYINT(3) UNSIGNED NOT NULL ,
 `designers_id` TINYINT(3) UNSIGNED NOT NULL ,
 `prices_id` TINYINT(3) UNSIGNED NOT NULL ,
 PRIMARY KEY (`id`) ,
 INDEX `category_id` (`category_id` ASC) ,
 INDEX `fk_products_categories` (`categories_id` ASC) ,
 INDEX `fk_products_designers1` (`designers_id` ASC) ,
 INDEX `fk_products_prices1` (`prices_id` ASC) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;

 

as you can see, we have a lot more foreign keys here for starters. This would make the stored procedures more complex for sure. But alas I'm not trying to get into a flex your muscles dialogue here, more peoples opinions or insights really.

Link to comment
Share on other sites

Ok, I thought you were struggling with something here. If you want a general discussion on your ER-diagram, sure we can do that. From the looks of it, your models look pretty good. However, there are some things that bother me a little bit here, or that I want to ask you about.

 

1. The table Prices. I don't get that entity. You should store the prices as floating point numbers. Your current solution is a one-to-one relationship that does not make sense to normalize. If you need the price table in another setting, do that, but remove the relationship to products.

2. Categories in products. Could some jeans fit in several categories? Might be worth thinking about.

3. Stock. This one is a bit tricky, so discard me if it's the wrong approach. A single pair of Jeans might come in 10 colors and 5 sizes. Currently, you would need to add 50 products. This is too complex to figure out while on the train, but you should think about this one. Is there a way to add a generalized jeans product and then add size, color and numbers of jeans? I don't know if that would work for you, but it would make many things easier.

 

Hope that was closer to what your after.

Link to comment
Share on other sites

Antonio, just to comment on #3: I think most sites would display the stock for each individual product, as opposed to an aggregate number.

For example, if you clicked on some jeans you wanted to buy, and there were 3 small, 4 medium, and 10 large, then I don't think 17 would be displayed for the stock. Instead, I think the number would be displayed for the default size, and as you select a different size, a different number would be fetched from the DB.

 

Am I wrong? (Actually, I don't think this is a matter of right or wrong, but a question of implementation.)

I get the feeling that most sites do things as I suggested, and I think that most shoppers prefer this, because, for example, if I want to buy small jeans, and the site tells me that there are 12 jeans still in stock, but none of them are small, I might get annoyed.

 

Of course, this also makes the DB queries a lot easier and less taxing.

 

Just my opinion.

Link to comment
Share on other sites

We fully agree, Jon. The question is how this can be modeled effectively. If you take a look at the current scheme, you would need to add 12 products no matter size, color, etc. My thinking is data a more generalized product, with extended information about the actuall products size, color and availability could be defined on top of that. That would allow you to add a single general product (Like "Nudie Jeans Slim-legged bootcut Nr. 10", but control how many "blue, medium" or "red small" are available in stock.

Link to comment
Share on other sites

Ok, I thought you were struggling with something here. If you want a general discussion on your ER-diagram, sure we can do that. From the looks of it, your models look pretty good. However, there are some things that bother me a little bit here, or that I want to ask you about.

 

1. The table Prices. I don't get that entity. You should store the prices as floating point numbers. Your current solution is a one-to-one relationship that does not make sense to normalize. If you need the price table in another setting, do that, but remove the relationship to products.

2. Categories in products. Could some jeans fit in several categories? Might be worth thinking about.

3. Stock. This one is a bit tricky, so discard me if it's the wrong approach. A single pair of Jeans might come in 10 colors and 5 sizes. Currently, you would need to add 50 products. This is too complex to figure out while on the train, but you should think about this one. Is there a way to add a generalized jeans product and then add size, color and numbers of jeans? I don't know if that would work for you, but it would make many things easier.

 

Hope that was closer to what your after.

 

thanks.

 

Yes, I could scratch the prices table maybe you have a point. A price should be in the Product table. I'm more comfortable with the singular term 'Product' as opposed to 'Products'.

 

I'm going to stick with categories and products as separate entities. One category can have multiple products and a single product belongs in one category: one to many.

 

Not sure about stock yet, mulling that over.

 

For me I really want to be able to search on: category, designer, price, size and colour and so am torn between the stored procedure call and database model.

 

My Stored Procedure SP may be similar to:

 

DELIMITER $$
CREATE PROCEDURE select_products(type VARCHAR(40), cat TINYINT)
BEGIN
IF type = 'category' THEN
SELECT ...
FROM product AS p
INNER JOIN category AS c ON p.id=c.category_id 
WHERE c.category_id=cat AND stock>0
ORDER by name ASC;
ELSEIF type = 'designer' THEN
SELECT
FROM product AS p
INNER JOIN designer AS d
ON p.id=d.designer_id
WHERE designer_id=cat AND stock>0
ORDER by date_created DESC;
ELSEIF type = 'colour' THEN
SELECT
FROM product AS p
INNER JOIN colour AS col
ON p.id=col.colour_id
WHERE colour_id=cat AND stock>0
ORDER by DESC;
ELSEIF type = 'size' THEN
...

END IF;
END$$
DELIMITER ;

 

unchecked draft of course. I would then have multiple forms in the front end to get the users interaction, filtering my SP results by any of the variables mentioned.

 

 

 

<select name="price">
<option value="19.99">19.99</option>
<option value="29.99">29.99</option>
<option value="39.99">39.99</option>
<option value="49.99">49.99</option>
</select>
<select name="colour">
<option value="red">red</option>
<option value="stonewash">stonewash</option>
<option value="tan">tan</option>
</select>
<select name="size">
<option value="32L">32L</option>
<option value="33M">33M</option>
<option value="34S">34S</option>
</select>

 

This is just off the top of my head. Good comments so far; thanks again for the help.

Link to comment
Share on other sites

Antonio, you have a point. I suppose you could further abstract the product table into something like general products, which would then have a one-to-many relationship with a table containing sizes, colors, etc.

 

I think either could work, and it starts to become a trade-off between better normalization or faster queries.

  • Upvote 1
Link to comment
Share on other sites

I don't fully agree, but I don't complete disagree neither, Jon. :P

 

When it comes to functionality, data integrity or clarity for users, I would not rank speed the highest. You don't need that extra information anyway in a product list/overview on second thought, so it would functionally be the same. (I guess) I think my idea might be a good solution, but I'm sure I missed some pitfalls.

 

Do you need to use stored procedures, though, 1cookie? This seems very limiting and rigid to me. What if you want to incorporate some new clauses? What might be a better idea is a standard GET-based search where search query alters by the param. As an example, how about:

 

domain.com/search.php?brand=nudie jeans

domain.com/search.php?category=new

 

I'm not sure if this is really a better idea, but that is what my sleep deprived brain would have done. Btw. It would be easy to include searches on all clauses, add filters and that kind of stuff with such a solution. If I'm not very good at explaining my thoughts, I can write a short demo tomorrow.

  • Upvote 1
Link to comment
Share on other sites

I wouldn't rank speed the highest either, but at the same time, normalizing your DB to 4NF is usually overkill.

 

Anyway, I'm not a expert on clothes, so it's hard for me to comment on this DB design. It just seemed like a three-layered approach would be best.

 

For example, I recently bought a suitcase on Amazon. In this case (no pun intended), the category could be "luggage", under which the general product would be the particular suitcase I bought, and the third level could contain all the colors, sizes, etc. available for that particular suitcase.

By structuring things this way, various searches are simple, and it makes it easy to display the page for a particular product (with all the available colors and sizes, etc.).

 

I must admit though that like you, Antonio, I may be skipping over some critical design caveat, but overall, a three-layer approach just "feels right".

 

If you have a better idea, by all means, please share it. I'm actually at a loss on this one myself.

Link to comment
Share on other sites

Do you need to use stored procedures, though, 1cookie?

 

 

You're right, it's staring me right in the face. URL params is a better way me thinks.

 

This seems very limiting and rigid to me. What if you want to incorporate some new clauses? What might be a better idea is a standard GET-based search where search query alters by the param. As an example, how about:

 

domain.com/search.php?brand=nudie jeans

domain.com/search.php?category=new

 

I'm not sure if this is really a better idea,

 

Do you mean like a bootstrap (index.php) file for instance? Similar to:

 

if (isset($_GET['p'])) {
$p = $_GET['p'];
} elseif (isset($_POST['p'])) { // Forms
$p = $_POST['p'];
} else {
$p = NULL;
}
switch ($p) {
case 'price':
 $page = new Price;
 break;

case 'size':
 $page = new Size;
	 break;

case 'colour':
 $page = new Colour;
 break;

default:
 $page = new Main;
 break;

} // switch.
if (!is_object( $page)) {
$page = new Main;

}
include('./includes/header.html');
$page->load();

include('./includes/footer.html');

 

as an example.

 

looking at the larger websites, they use URLs like:

 

http://www.example.com/men/jeans#catalogId=10001&lid=//productsuniverse/en_GB/product_online%3DY/categories%3C{productsuniverse_18664}/categories%3C{productsuniverse_18664_18546}/categories%3C{productsuniverse_18664_18546_18551_ms}&ps=default&sfn=CATEGORIES&sfv=Bootcut&storeId=10001

 

Just for the record, I'm not looking to code the next Magento site here (I'm not nearly clever enough), it's the small scale stuff that I'm learing with to start with.

 

but that is what my sleep deprived brain would have done. Btw. It would be easy to include searches on all clauses, add filters and that kind of stuff with such a solution. If I'm not very good at explaining my thoughts, I can write a short demo tomorrow.

 

That sounds interesting, I look forward to that. I'm doing this in my spare time in between work hours so forgive the pauses; although its going around in my head most of the time :). For the URLs - If we were talking about frameworks we'd be talking about routes no doubt. I'm not looking to implement a framework though. I am looking to code it as a collection of objects or OOP however.

Link to comment
Share on other sites

  • 1 month later...

You might want to think along totally different lines.  In a clothing store or Wal-Mart, each different item has a unique UPC code, so the store knows what to reorder and what is sold very easily.  Yes, this means making 50 product IDs for a pair of jeans in 10 sizes and 5 colors, but then you know EXACTLY what is selling and what to reorder.  Unless you are having the products drop-shipped, then inventory costs can be very high, and fashions change quickly.

 

If you use 1 main product ID with variants for size and color, then you can easily associate all the "Farmer John" jeans of the various sizes and colors on one page, and all the "Slim Capri" jeans would show on another page. But you will get more back end info by using categories such as Overalls and Capris, using a separate SKU for each item, and then making a landing page that uses a bit of php code to populate the page based on given parameters.  This would also allow you to group all the Farmers Johns on one page, and get better control of your stock, what is selling, what needs to be re-ordered, etc.

 

Look online at various stores that carry clothing, such as Wal-Mart, Sears, JCPenney, Land's End, Brooks Brothers, etc.  Pay close attention to how easy it is to find what you are looking for.  What kinds of filters would make the search easier?  By price, by color, by size, by body type, by waist or inseam size, think of all the possibilities!  Of course, this means you have to code all this info into the db, but once done, you will have a very solid back end that will allow you to change the look and feel of your site very easily.

 

Be careful about locking yourself into only one category per item.  If you later decide to have a "Sale" or "Clearance" category, then you have to make a choice of showing the item under clearance or the usual category, but not both.  The same holds true if you want to use categories like "heavy duty work wear" and "overalls"; how would you fit these overalls into both categories?

 

Clothing is very seasonal, so you might want to think about changing the theme of you site periodically to reflect the changing seasons, various holidays or patriotic events, etc.  This would usually indicate featuring something in particular, such as all green clothing on sale for St. Patrick's Day.

  • Upvote 1
Link to comment
Share on other sites

 Share

×
×
  • Create New...