MySQL Forums
Forum List  »  Newbie

Opinions on this schema please =)
Posted by: Willem vanSchevikhoven
Date: February 19, 2009 03:34PM

Well, i have finally got something worked out on my schema and i'd very much like some feedback on weather im doing things the right way or the wrong way.

UPDATE 21.2.2009:
*************************
Hmm, i can post my new reply to my topic for some strange reason... Anyways, here's my update as i intended to post it:
Thank you for the input Peter and Rick!

The generic method is very important to me since what i have in mind is something like www.summitracing.com. They seem to have a lot of different properties which are all a part of the search for a suitable article. The problem with a set of columns directly on the product is that i just simply cant predict all the properties an item or article might have, we might get a new item which requires us to use a new type of property. off my head we could need at least a 100 columns for properties (rim offset, rim width, rim diameter, engine type etc) and we would only be using like 5 to 15 of them at a time.

The update speed is not one of my biggest concerns (or should it be?) as the main focus is to be able to provide a fast database for clients browsing products. The updates we make are not that important since the guys making an update are paid to do so. Clients that browse a slow website are most likely moving to an other shop since they have no strings attached.

I'm also have a fixation that tags should only be stored for one item or article at a time since storing two similar properties for an item and it's corresponding article might lead into big hassles if we fail to update the other tag too. Imagine inserting an alloy rim with the wrong offset, updating only one tag for the offset might lead to some very unpleasant situations if the client or our staffmember is misinformed by the database.

I could ofcourse take away the properties "master" table, and just have the item_properties and article_properties with an indexed property name. That would atleast get rid of two FROM (select ... join )'s.
This method will make managing tags a bit more messed up, but i thinki could live with it.:

SELECT DISTINCT II2A.item_id, II2A.article_id FROM
ITEM_PROPERTIES AS IP,

(SELECT I2A.item_id, I2A.article_id FROM
(SELECT ITEMS.item_id FROM ITEMS WHERE
ITEMS.item_price = 1890) AS I JOIN I2A
ON I2A.item_id = I.item_id) AS II2A,

ARTICLE_PROPERTIES AS AP

WHERE
(II2A.item_id = IP.item_id AND II2A.article_id = AP.article_id) AND
((IP.property_name = 'make' AND IP.property_value = 'toyota') OR
(AP.property_name = 'make' AND AP.property_value = 'toyota')) AND

((IP.property_name = 'model' AND IP.property_value = 'supra') OR
(AP.property_name = 'model' AND AP.property_value = 'supra'));

If i could make a trigger to keep the item or article tags up to date for the item's and articles columns. (the price for example is a property that's a row on the item). The query could be written like:

SELECT DISTINCT I2A.item_id, I2A.article_id FROM

ITEM_PROPERTIES AS IP, I2A, ARTICLE_PROPERTIES AS AP

WHERE
(I2A.item_id = IP.item_id AND I2A.article_id = AP.article_id) AND

((IP.property_name = 'price' AND IP.property_value = '1980') OR
(AP.property_name = 'price' AND AP.property_value = '1980')) AND

((IP.property_name = 'make' AND IP.property_value = 'toyota') OR
(AP.property_name = 'make' AND AP.property_value = 'toyota')) AND

((IP.property_name = 'model' AND IP.property_value = 'supra') OR
(AP.property_name = 'model' AND AP.property_value = 'supra'));

Am i on the right tracks by any chance? Or am i just hitting my head on the wall?

*******END UPDATE 21.2.2009 **************************************

EDIT: To put it out simply:
My goal is to be able to categorize items with something like "make:nissan;+model:200sx s13;+brand:Cusco;-part_type:coilover"
rather than just a simple category id while keeping actual warehousable items and the articles in the webshop separate entities with an n:m relationship.
Also, items and articles need to have some kind of eav -system which can be searched with set like above.

Here is a link to the drawing (click to enlarge)
http://s50.photobucket.com/albums/f341/Chezecow/stuff/?action=view&current=dbsketch.png

Anyways. The idea behind the schema is that items and articles have a n:m relationship. In other words items are real items, and articles are items that are displayed in a webshop for example. One item can be a part of one or more articles, and an article can also have multiple items pointing towards it.

The third table, properties, is something that resembles some kind of eav -stuff. The idea behind this is to be able to give items and articles a shared pool of properties like diameter or color. we can search for articles that are either directly, or through an item connected to lets say Nissan, and have a pillowball topmount. For example: "make:nissan;+topmount:pillowball;"

I havent got a chance to test this out on real data yet, and as i am a performance freak i'd like some feedback on the sanity of this schema. Am i trying to swim over the river when there's a bridge just next to me :)

The actual query i'm working on is here, some test data and created are after this if someone cares to try this out.
****************************************************
SELECT DISTINCT II2A.item_id, II2A.article_id FROM
(SELECT ITEM_PROPERTIES.property_value, PROPERTIES.property_name, ITEM_PROPERTIES.item_id FROM
ITEM_PROPERTIES JOIN PROPERTIES ON ITEM_PROPERTIES.property_id = PROPERTIES.property_id) AS IP,

(SELECT I2A.item_id, I2A.article_id FROM
(SELECT ITEMS.item_id FROM ITEMS WHERE
ITEMS.item_attr1 = 1890) AS I JOIN I2A
ON I2A.item_id = I.item_id) AS II2A,

(SELECT ARTICLE_PROPERTIES.property_value, PROPERTIES.property_name, ARTICLE_PROPERTIES.article_id FROM
ARTICLE_PROPERTIES JOIN PROPERTIES ON ARTICLE_PROPERTIES.property_id = PROPERTIES.property_id) AS AP

WHERE
(II2A.item_id = IP.item_id AND II2A.article_id = AP.article_id) AND
((IP.property_name = 'make' AND IP.property_value = 'toyota') OR
(AP.property_name = 'make' AND AP.property_value = 'toyota')) AND

((IP.property_name = 'make' AND IP.property_value = 'toyota') OR
(AP.property_name = 'make' AND AP.property_value = 'toyota'));
#********************************************************
#
#Creates:
#**********************************************************
#
# ITEM-ARTICLE -Region creates.
#

# Items table
CREATE TABLE ITEMS (
item_id INT NOT NULL AUTO_INCREMENT,
item_name VARCHAR(50),
item_attr1 INT,
PRIMARY KEY (item_id))
ENGINE = INNODB;

# Articles table
CREATE TABLE ARTICLES (
article_id INT NOT NULL AUTO_INCREMENT,
article_name VARCHAR(50),
article_attr1 INT,
PRIMARY KEY (article_id))
ENGINE = INNODB;

# Properties table
CREATE TABLE PROPERTIES (
property_id INT NOT NULL AUTO_INCREMENT,
property_name VARCHAR(128) NOT NULL,
INDEX (property_id, property_name),
PRIMARY KEY (property_id),
UNIQUE KEY (property_name))
ENGINE = INNODB;

# Content table for identifying different pieces of localized content.
CREATE TABLE CONTENT (
content_id INT NOT NULL AUTO_INCREMENT,
content_name VARCHAR (50) NOT NULL,
content_type VARCHAR (20),
PRIMARY KEY (content_id),
INDEX (content_id, content_name))
ENGINE = INNODB;

# CONTENT_LOCALES table for storing the actual data on content with a locale tag.
CREATE TABLE CONTENT_LOCALE (
content_locale_id INT NOT NULL AUTO_INCREMENT,
content_locale_language VARCHAR(3) NOT NULL,
content_locale_data TEXT NOT NULL,
content_id INT NOT NULL,
PRIMARY KEY (content_locale_id),
INDEX (content_locale_language, content_locale_id, content_id),
FOREIGN KEY (content_id) REFERENCES CONTENT (content_id)
ON UPDATE CASCADE ON DELETE CASCADE)
ENGINE = INNODB;

# Join table for Items and articles
CREATE TABLE I2A (
i2a_id INT NOT NULL AUTO_INCREMENT,
item_id INT NOT NULL,
article_id INT NOT NULL,
INDEX(item_id, article_id),
PRIMARY KEY (i2a_id),
FOREIGN KEY (item_id) REFERENCES ITEMS (item_id)
ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY (article_id) REFERENCES ARTICLES (article_id)
ON UPDATE CASCADE ON DELETE CASCADE)
ENGINE = INNODB;

# Join table for items and properties housing the value
CREATE TABLE ITEM_PROPERTIES (
item_property_id INT NOT NULL AUTO_INCREMENT,
property_value VARCHAR(128),
item_id INT NOT NULL,
property_id INT NOT NULL,
INDEX(item_id, property_value, property_id),
PRIMARY KEY (item_property_id),
FOREIGN KEY (item_id) REFERENCES ITEMS (item_id)
ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (property_id) REFERENCES PROPERTIES (property_id)
ON UPDATE CASCADE ON DELETE CASCADE)
ENGINE = INNODB;

# Join table for articles and properties housing the value
CREATE TABLE ARTICLE_PROPERTIES (
article_property_id INT NOT NULL AUTO_INCREMENT,
property_value VARCHAR(128),
article_id INT NOT NULL,
property_id INT NOT NULL,
INDEX(article_id, property_value, property_id),
PRIMARY KEY (article_property_id),
FOREIGN KEY (article_id) REFERENCES ARTICLES (article_id)
ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (property_id) REFERENCES PROPERTIES (property_id)
ON UPDATE CASCADE ON DELETE CASCADE)
ENGINE = INNODB;

# TESTDATA
#
# Data insertion for testing
#

# Properties
INSERT INTO PROPERTIES (property_name) VALUES
('make'),
('model'),
('topmount');

# Items
INSERT INTO ITEMS (item_name, item_attr1) VALUES
('D2-N021-PBTM', 990),
('D2-N021-RBTM', 890),
('D2-N022-PBTM', 990),
('D2-N022-RBTM', 890),
('D2-N023-PBTM', 1990),
('D2-N023-RBTM', 890),
('D2-T01-PBTM', 1990),
('D2-T01-RBTM', 1890);

# Topmount types for the items.
INSERT INTO ITEM_PROPERTIES (property_value, item_id, property_id) VALUES
('Pillowball', 1, 3),
('Rubber', 2, 3),
('Pillowball', 3, 3),
('Rubber', 4, 3),
('Pillowball', 5, 3),
('Rubber', 6, 3),
('Pillowball', 7, 3),
('Rubber', 8, 3);

# Insert four corresponding webshop articles.
INSERT INTO ARTICLES (article_name) VALUES
('Coiloversarja Nissan 200sx S13'),
('Coiloversarja Nissan 200sx S14'),
('Coiloversarja Nissan 200sx S15'),
('Coiloversarja Toyota Supra MK4');

# Assign properties and values for the articles.
INSERT INTO ARTICLE_PROPERTIES (property_value, article_id, property_id) VALUES
('Nissan', 1, 1),
('Nissan', 2, 1),
('Nissan', 3, 1),
('Toyota', 4, 1),
('200sx s13', 1, 2),
('200sx s13', 2, 2),
('200sx s13', 3, 2),
('Supra MK4', 4, 2);

INSERT INTO I2A (item_id, article_id) VALUES
(1, 1),
(2, 1),
(3, 2),
(4, 2),
(5, 3),
(6, 3),
(7, 4),
(8, 4);



Edited 3 time(s). Last edit at 02/21/2009 08:09AM by Willem vanSchevikhoven.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.