Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
[Database design]This look right for a store?[Updated]
View unanswered posts
View posts from last 24 hours

 
Reply to topic    Gentoo Forums Forum Index Off the Wall
View previous topic :: View next topic  
Author Message
Shadow Skill
Veteran
Veteran


Joined: 04 Dec 2004
Posts: 1023

PostPosted: Mon Nov 19, 2012 1:23 am    Post subject: [Database design]This look right for a store?[Updated] Reply with quote

See rough ERD here.

Things I know are missing:
A session handling table. (The host doesn't allow writes to the disk for things like session data.)
A view to make displaying order info easier.
A payment info table. (Have to ask the client what Paypal service they use before I bother modeling it.)

I'm trying to implement a design where the item options can be specific to a sub type of any given item. I was inspired by this stackoverflow post. I'm not sure I am getting the basic design for this sort of thing correct though. Suggestions/Flames welcome. (Well maybe not flames. :))



This is a clothing store. (A real one.)


*Updated as per Prenj and Bk's suggestions.

I think I have managed to solve the whole hierarchy representation problem without the need for type specific join tables. While looking for information on how to represent a tree as a relation I came across several articles and a forum post detailing two methods other than what is called the adjacency table model. This article details with pictures a nested set solution, and This article uses the adjacency model but encodes the depth and node lineage inside the table explicitly. There is also a post in the comments for this last article that talks about the nested set solution but it doesn't have pictures so it may be less understandable.
_________________
Ware wa mutekinari.
Wa ga kage waza ni kanau mono nashi.
Wa ga ichigeki wa mutekinari.

"First there was nothing, so the lord gave us light. There was still nothing, but at least you could see it."


Last edited by Shadow Skill on Sun Nov 25, 2012 10:50 pm; edited 2 times in total
Back to top
View user's profile Send private message
Bones McCracker
Veteran
Veteran


Joined: 14 Mar 2006
Posts: 1569
Location: U.S.A.

PostPosted: Mon Nov 19, 2012 3:44 am    Post subject: Reply with quote

Looks almost usable. Here is what I see:

Narrative: Each "Product" comes in one or more "types", each of which has a unique combination of size, color, and pattern, and may or may not have additional distinguishing characteristics. Users, who may or may not be customers or administrators, have one or more contacts, which may or may not be used for billing, and they may place orders, which contain items and are shipped to one of their contacts.

A couple of thoughts:

1. I don't see why you have specific combinations of general attributes (size, color, pattern) in a separate table. Why not put those attributes right in the "product type" table? If you do, it may help maintain data integrity to have those fields (size, color, pattern, now in the product type table) hold only foreign keys (i.e., instead be size-id, color-id, and pattern-id, referring to a separate "sizes" table, "colors" table, and "patterns" table).

2. Naming is important part of database design, because it clarifies, for yourself, what you are doing. It also makes it more understandable for others later. Ambiguity or inaccuracy in naming is dangerous.

a. I use plural nouns for table names. For example, where you have "User", I would have the less ambiguous and also plural "customers". I don't think you really need to model "users" (to include the operators and administrators of the system) here.

b. I probably would have named your "product Type" table something like "models". "product Type" could be any independent classification (e.g., "clothing" vs "hardware"), as opposed to a hierarchical decomposition of each general product into specific products. You may be constrained to your client's terminology, but you should probe them to see what this thing really is (it might be a "SKU" or an "inventory-item", or the like). As an example of the kind of problem ambiguous naming leads to, you have in your "order items" table a foreign key "product_id" which I believe should be "type_id" (or, if you follow my suggestion, SKU-id or inventory-id). By the way, there should probably also be a "quantity" field in that "order items" table (which I would call "order_items", the underscore being my convention to indicate it's a join table (or "fact table") joining with foreign keys the tables "orders" and "inventory-items" (currently your "product Type". Conventions are up to you, just be consistent.

3. Where you have "Contact", I would have "addresses", because that's what's really there (contacts are people). The relationships to that table are sketchy. I think both users and orders should have relationships to addresses. Right now, you've got a foreign key "user_Id" in your "contact" table, but no relationship to the user table (only to the orders table).

You may need to make use of join tables to adequately model the relationships. For example, you could have a users table and an addresses table (with a foreign key to user_id). You could model the shipping and billing addresses in the form of two join tables, one called user_shipping-addresses and one called user_billing-addresses; these tables would contain nothing but two fields each and be structurally identical, each contain a foreign key user-id and a foreign key address-id. They just store facts: what are the users' billing and shipping addresses. In the user interface, you allow the user to enter addresses, and designate one (and only one) as their billing address of record and one (and only one) as their default shipping address. Then, in the orders table, you also put a foreign key to addresses, with the order entry interface being populated by default with the user's default shipping address, but with the user interface providing the option to select a different one of the user's addresses or enter a new address.

4. I don't see anything having to do with money. This is another whole can of worms. :lol:
_________________
Naib wrote:
you need a dick sometimes to deal with the assholes
Back to top
View user's profile Send private message
John-Boy
Guru
Guru


Joined: 23 Jun 2004
Posts: 439
Location: Desperately seeking moksha in all the wrong places

PostPosted: Mon Nov 19, 2012 5:28 am    Post subject: Reply with quote

Contact table jumps out - repeating data items there.
_________________
It's later than you think
Back to top
View user's profile Send private message
Prenj
n00b
n00b


Joined: 20 Nov 2011
Posts: 13

PostPosted: Mon Nov 19, 2012 6:42 am    Post subject: Reply with quote

Hard to tell since we don't know what kind of products you are selling, but lets consider BWM dealership.

Products would be cars, so product ID would correspond to the model. Options may vary per model, as in you have a certain range of say engines associated with certain model, and for example you cannot get every car in every color. You may want to normalize the design so that all the options have their own ID (every color would have its own ID, etc) and that the user would be unable to select an option that is not available for a certain model. In other words selection (combo box) would be populated only by available options for model selection.

So for color, you'd need a products table, colors table and a cross-ref product_colors table that would be similar to your order_items table.
Same for options I guess.

Alternatively, if you don't normalize, that is, if you put all options/colors in main table, you don't have definition of valid colors or options, and may be forced to use text value, which is how bad database entries are created, and you would have no way of limiting the customer to available options only, since you don't have definition.

So I'd say your approach of normalizing types and options is good idea, but it seems like you are stuck in between a generalized "product_type" definition and specific ones.

If you do VW car dealership example, you might have following hierarchy:


model (Passat, Golf, etc)

in that sense, model is your primary data, since everything else relates to it, and series is descriptive data, since engine size and whatnot is not associated with series, but with a model.

so you have table models:

models
model_ID (PK)
model_description
baseline_price

then you have cross ref tables for options per model

models_engines
model_ID(PK)(FK from models)
engine_ID(PK)(FK from engines)

where engines could like like

engines
engine_ID (PK)
engine_description
price_modifier

and so on for all other types of extra options that would be selected in "next step" (in other words you select chassie, engine, color, interior colors/materials, stereo, whatnot in different steps)

then when customer is making his own order, he is "configuring" a car and selecting
model_ID
engine_ID
color_ID
which is stored in orders table.

Not sure if it helps, but the point is, options per something (subtype, model, whatever) usually demand its own cros_ref table so that you can fully define an option, and then the logic of what goes with what via cross_ref table.
Back to top
View user's profile Send private message
big dave
n00b
n00b


Joined: 03 Jul 2009
Posts: 0
Location: land of first world problems

PostPosted: Mon Nov 19, 2012 10:15 am    Post subject: Reply with quote

you're missing the key value store... relational is dead and doesn't scale. zing!
Back to top
View user's profile Send private message
Bigun
Veteran
Veteran


Joined: 21 Sep 2003
Posts: 1974

PostPosted: Mon Nov 19, 2012 3:03 pm    Post subject: Reply with quote

big dave wrote:
you're missing the key value store... relational is dead and doesn't scale. zing!


++

Rule #1 of any table - Each value must have a unique key/identifier.
Back to top
View user's profile Send private message
Bones McCracker
Veteran
Veteran


Joined: 14 Mar 2006
Posts: 1569
Location: U.S.A.

PostPosted: Mon Nov 19, 2012 4:39 pm    Post subject: Reply with quote

These other guys are all confused. Ignore them or you'll be confused too. :P

big dave's got a point, but that's an implementation detail. First model the data, aiming for 3NF. bigun agreed with big dave, but I suspect he didn't actually understand what big dave was saying.

prenj appears to not understand the scenario in terms of the product hierarchy.

I think john-boy got confused by your missing relationship and thought a foreign key was repeated data.
_________________
Naib wrote:
you need a dick sometimes to deal with the assholes
Back to top
View user's profile Send private message
John-Boy
Guru
Guru


Joined: 23 Jun 2004
Posts: 439
Location: Desperately seeking moksha in all the wrong places

PostPosted: Mon Nov 19, 2012 5:33 pm    Post subject: Reply with quote

BoneKracker wrote:
I think john-boy got confused by your missing relationship and thought a foreign key was repeated data.


It's the city, state, country and postcode (zip), these elements should be split out.

if you're being picky & bearing in mind design constraints - denormalise for speed etc.

Reminds me, once worked on system that had pig's ear type relationship re: address data.

The queries hurt. A lot.
_________________
It's later than you think
Back to top
View user's profile Send private message
Bones McCracker
Veteran
Veteran


Joined: 14 Mar 2006
Posts: 1569
Location: U.S.A.

PostPosted: Mon Nov 19, 2012 6:37 pm    Post subject: Reply with quote

Oh, I see what you mean. They could be drawn from separate tables of "cities", "states", and "postal-codes". That's true.

John Boy is not confused.
_________________
Naib wrote:
you need a dick sometimes to deal with the assholes
Back to top
View user's profile Send private message
Prenj
n00b
n00b


Joined: 20 Nov 2011
Posts: 13

PostPosted: Mon Nov 19, 2012 6:48 pm    Post subject: Reply with quote

BoneKracker wrote:
prenj appears to not understand the scenario in terms of the product hierarchy.


Well spotted, I don't. It's why I went on tanget with another hypothethical example.
Back to top
View user's profile Send private message
Bones McCracker
Veteran
Veteran


Joined: 14 Mar 2006
Posts: 1569
Location: U.S.A.

PostPosted: Mon Nov 19, 2012 6:53 pm    Post subject: Reply with quote

Prenj wrote:
BoneKracker wrote:
prenj appears to not understand the scenario in terms of the product hierarchy.


Well spotted, I don't. It's why I went on tanget with another hypothethical example.

I'm just busting your nuts. :P
_________________
Naib wrote:
you need a dick sometimes to deal with the assholes
Back to top
View user's profile Send private message
John-Boy
Guru
Guru


Joined: 23 Jun 2004
Posts: 439
Location: Desperately seeking moksha in all the wrong places

PostPosted: Mon Nov 19, 2012 6:54 pm    Post subject: Reply with quote

BoneKracker wrote:
Oh, I see what you mean. They could be drawn from separate tables of "cities", "states", and "postal-codes". That's true.

John Boy is not confused.


Oh I am. On a daily basis, this doesn't involve balance sheets however - so I'm on firmer ground.

OP, if this is for an assignment - I'd say certainly state/country as separate tables *. You could probably argue for performance increase
from accepting a wee bit of redundancy with postcodes heaped together (depends on your tutor). Anything beyond 3NF is generally
redundant and a bit strange, you could argue that for 3NF itself.

( * Or tread the path of a recursive relationship, parent/child fields)
_________________
It's later than you think
Back to top
View user's profile Send private message
Prenj
n00b
n00b


Joined: 20 Nov 2011
Posts: 13

PostPosted: Mon Nov 19, 2012 7:06 pm    Post subject: Reply with quote

John-Boy wrote:
OP, if this is for an assignment - I'd say certainly state/country as separate tables *. You could probably argue for performance increase
from accepting a wee bit of redundancy with postcodes heaped together (depends on your tutor). Anything beyond 3NF is generally
redundant and a bit strange, you could argue that for 3NF itself.


Well while it often looks like less normalization = faster due to less complicated select statemens and fewer joins needed, my experience is that it is not always true in practice. A lot of normalized information in separate table is as good as static. For example countries table. You populate that once, and then update it every time there is an U.N. resolution. Clients can cache it, and then when user selects "Denmark", you get an ID value, say 21, and then you search your main table with an ID. You don't join, and you don't search for a string either. But you do keep good form and data, thanks to 3NF.
Back to top
View user's profile Send private message
Prenj
n00b
n00b


Joined: 20 Nov 2011
Posts: 13

PostPosted: Mon Nov 19, 2012 7:07 pm    Post subject: Reply with quote

BoneKracker wrote:
Prenj wrote:
BoneKracker wrote:
prenj appears to not understand the scenario in terms of the product hierarchy.


Well spotted, I don't. It's why I went on tanget with another hypothethical example.

I'm just busting your nuts. :P


Oh, ok, my brain is not working properly today, coffee has no effect whatsoever :)
Back to top
View user's profile Send private message
Shadow Skill
Veteran
Veteran


Joined: 04 Dec 2004
Posts: 1023

PostPosted: Mon Nov 19, 2012 9:43 pm    Post subject: Reply with quote

BoneKracker wrote:
Looks almost usable. Here is what I see:

Narrative: Each "Product" comes in one or more "types", each of which has a unique combination of size, color, and pattern, and may or may not have additional distinguishing characteristics. Users, who may or may not be customers or administrators, have one or more contacts, which may or may not be used for billing, and they may place orders, which contain items and are shipped to one of their contacts.

A couple of thoughts:

1. I don't see why you have specific combinations of general attributes (size, color, pattern) in a separate table. Why not put those attributes right in the "product type" table? If you do, it may help maintain data integrity to have those fields (size, color, pattern, now in the product type table) hold only foreign keys (i.e., instead be size-id, color-id, and pattern-id, referring to a separate "sizes" table, "colors" table, and "patterns" table).

2. Naming is important part of database design, because it clarifies, for yourself, what you are doing. It also makes it more understandable for others later. Ambiguity or inaccuracy in naming is dangerous.

a. I use plural nouns for table names. For example, where you have "User", I would have the less ambiguous and also plural "customers". I don't think you really need to model "users" (to include the operators and administrators of the system) here.

b. I probably would have named your "product Type" table something like "models". "product Type" could be any independent classification (e.g., "clothing" vs "hardware"), as opposed to a hierarchical decomposition of each general product into specific products. You may be constrained to your client's terminology, but you should probe them to see what this thing really is (it might be a "SKU" or an "inventory-item", or the like). As an example of the kind of problem ambiguous naming leads to, you have in your "order items" table a foreign key "product_id" which I believe should be "type_id" (or, if you follow my suggestion, SKU-id or inventory-id). By the way, there should probably also be a "quantity" field in that "order items" table (which I would call "order_items", the underscore being my convention to indicate it's a join table (or "fact table") joining with foreign keys the tables "orders" and "inventory-items" (currently your "product Type". Conventions are up to you, just be consistent.

3. Where you have "Contact", I would have "addresses", because that's what's really there (contacts are people). The relationships to that table are sketchy. I think both users and orders should have relationships to addresses. Right now, you've got a foreign key "user_Id" in your "contact" table, but no relationship to the user table (only to the orders table).

You may need to make use of join tables to adequately model the relationships. For example, you could have a users table and an addresses table (with a foreign key to user_id). You could model the shipping and billing addresses in the form of two join tables, one called user_shipping-addresses and one called user_billing-addresses; these tables would contain nothing but two fields each and be structurally identical, each contain a foreign key user-id and a foreign key address-id. They just store facts: what are the users' billing and shipping addresses. In the user interface, you allow the user to enter addresses, and designate one (and only one) as their billing address of record and one (and only one) as their default shipping address. Then, in the orders table, you also put a foreign key to addresses, with the order entry interface being populated by default with the user's default shipping address, but with the user interface providing the option to select a different one of the user's addresses or enter a new address.

4. I don't see anything having to do with money. This is another whole can of worms. :lol:


1. That is a good idea. Though I am not sure how breaking down breaking down the options into separate static tables would help things, at least when compared to having a general options table. The reason I have a "general options" table is because the client allows customers to request custom sizes. I wanted a way to separate services from goods in this case things like shirts etc.

If I merged the "product type" together with the "general options" table how would I represent the possible subtypes of goods as a tree, while still maintaining a way to separate goods and services from one another? It does occur to me now that with my current design I need to have a category table for goods in order to have sub types for general categories like shirts,and pants etc.

2. Noted.

3. That is a better name for it. The "address" table has a foreign key to the user table. I screwed up my arrows, the arrow from orders to "addresses" should be the other way around.

4. That could be a thread on its' own, but what might you suggest considering that Paypal will be handling all of the payment processing. (Thankfully, I dread even thinking about storing ccinfo myself.)
_________________
Ware wa mutekinari.
Wa ga kage waza ni kanau mono nashi.
Wa ga ichigeki wa mutekinari.

"First there was nothing, so the lord gave us light. There was still nothing, but at least you could see it."
Back to top
View user's profile Send private message
jonnevers
Veteran
Veteran


Joined: 02 Jan 2003
Posts: 1593
Location: Gentoo64 land

PostPosted: Tue Nov 20, 2012 12:53 am    Post subject: Reply with quote

you might want to consider some of the really great NoSQL options. relational databases ( and hardcore static schemas ) aren't the end all be all of database design... especially when it comes to something like ecommerce. unless of course you like performance tuning RDBMS databases.

my second thought would be "tables are great... but what are your indexes?" with RDBMS you're indexes are your life blood, certainly more important then the specific names you give to tables ( which by the way shouldn't be plural but i guess this is preference ).

third thought, are you doing straight SQL or wading into ORM? if ORM.... consider document based databases ( such as mongo ) which are essentially huge object caches.
Back to top
View user's profile Send private message
Bones McCracker
Veteran
Veteran


Joined: 14 Mar 2006
Posts: 1569
Location: U.S.A.

PostPosted: Tue Nov 20, 2012 1:09 am    Post subject: Reply with quote

Before worrying about technical implementation details, you have to model the information.

Conceptual / functional design should be free of concerns about things such as query optimization, performance tuning, and the like. Model the information, understand the problems and requirements, understand the solution conceptually and functionally, then design the solution from a technical perspective.

If you don't correctly understand what you need to build (and the naming of entities is a part of that), before you start designing it, then you're fucked. Indexes and other implementation details don't mean dick, if you don't have the data model right to begin with. In my experience, misstated or misunderstood requirements and shitty conceptual/functional design are far more often the cause of project failures than technical challenges. Far too often, people simply build the wrong thing.

Also, the closer a model resembles the real-world situation, the more likely the system will survive over time, because it's less likely to need to be completely scrapped in order to provide additional functionality. Even if it can't be implemented that way, the implementation should start from a design that was based on the real world. If you design from the technical end, you create systems that end up being inelegant, hackish, full of work-around to accommodate modifications, and they typically end up becoming performance nightmares.

An E-R diagram should be a work of art -- elegant simplicity that accurately reflects the real world information. It's worth putting the effort into it to get it right before you move on, and it's routine to get the advice of others.

When you're done, an uninvolved person not familiar with the project or the organization should be able to construct from it a narrative description that would be fully agreed to by the client.

Also, before you ever get into technical design, you've also got to conceptually / functionally design the user interface and processing logic. That is likely to drive changes to the data model as well.

When you've done all that, and possibly even after you've created a prototype, then you can move on to technical design, including a database design based on the basic data model.
_________________
Naib wrote:
you need a dick sometimes to deal with the assholes
Back to top
View user's profile Send private message
jonnevers
Veteran
Veteran


Joined: 02 Jan 2003
Posts: 1593
Location: Gentoo64 land

PostPosted: Tue Nov 20, 2012 3:41 am    Post subject: Reply with quote

BoneKracker wrote:
Before worrying about technical implementation details, you have to model the information.

Conceptual / functional design should be free of concerns about things such as query optimization, performance tuning, and the like. Model the information, understand the problems and requirements, understand the solution conceptually and functionally, then design the solution from a technical perspective.

If you don't correctly understand what you need to build (and the naming of entities is a part of that), before you start designing it, then you're fucked. Indexes and other implementation details don't mean dick, if you don't have the data model right to begin with. In my experience, misstated or misunderstood requirements and shitty conceptual/functional design are far more often the cause of project failures than technical challenges. Far too often, people simply build the wrong thing.

Also, the closer a model resembles the real-world situation, the more likely the system will survive over time, because it's less likely to need to be completely scrapped in order to provide additional functionality. Even if it can't be implemented that way, the implementation should start from a design that was based on the real world. If you design from the technical end, you create systems that end up being inelegant, hackish, full of work-around to accommodate modifications, and they typically end up becoming performance nightmares.

An E-R diagram should be a work of art -- elegant simplicity that accurately reflects the real world information. It's worth putting the effort into it to get it right before you move on, and it's routine to get the advice of others.

When you're done, an uninvolved person not familiar with the project or the organization should be able to construct from it a narrative description that would be fully agreed to by the client.

Also, before you ever get into technical design, you've also got to conceptually / functionally design the user interface and processing logic. That is likely to drive changes to the data model as well.

When you've done all that, and possibly even after you've created a prototype, then you can move on to technical design, including a database design based on the basic data model.

ffs dude... thanks for the lecture, dad. must'a struck a nerve. wow.
Back to top
View user's profile Send private message
Bones McCracker
Veteran
Veteran


Joined: 14 Mar 2006
Posts: 1569
Location: U.S.A.

PostPosted: Tue Nov 20, 2012 4:29 am    Post subject: Reply with quote

:sigh: I apologize. I guess I was having a flashback. :?
_________________
Naib wrote:
you need a dick sometimes to deal with the assholes
Back to top
View user's profile Send private message
John-Boy
Guru
Guru


Joined: 23 Jun 2004
Posts: 439
Location: Desperately seeking moksha in all the wrong places

PostPosted: Tue Nov 20, 2012 5:59 am    Post subject: Reply with quote

BoneKracker wrote:
When you've done all that, and possibly even after you've created a prototype, then you can move on to technical design


Using MS-Access
_________________
It's later than you think
Back to top
View user's profile Send private message
sikpuppy
n00b
n00b


Joined: 12 Jun 2012
Posts: 34
Location: Central Coast, NSW

PostPosted: Tue Nov 20, 2012 6:07 am    Post subject: Reply with quote

John-Boy wrote:
BoneKracker wrote:
When you've done all that, and possibly even after you've created a prototype, then you can move on to technical design


Using MS-Access

Any hints on cleaning spat coffee from monitor bezels?
Back to top
View user's profile Send private message
Shadow Skill
Veteran
Veteran


Joined: 04 Dec 2004
Posts: 1023

PostPosted: Sun Nov 25, 2012 10:51 pm    Post subject: Reply with quote

John-Boy wrote:
BoneKracker wrote:
When you've done all that, and possibly even after you've created a prototype, then you can move on to technical design


Using MS-Access
Let's not talk about that.

Anyway see updates to OP.
_________________
Ware wa mutekinari.
Wa ga kage waza ni kanau mono nashi.
Wa ga ichigeki wa mutekinari.

"First there was nothing, so the lord gave us light. There was still nothing, but at least you could see it."
Back to top
View user's profile Send private message
Bones McCracker
Veteran
Veteran


Joined: 14 Mar 2006
Posts: 1569
Location: U.S.A.

PostPosted: Mon Nov 26, 2012 4:06 am    Post subject: Reply with quote

The "order items" table has a field, "product id", which I assume is a foreign key referring to the "id" field of the products table. I think an "order item" would instead need to refer to the specific model being ordered? Also, are the models different prices? If so, does price belong in the products table or the models table?

Options: I'm not sure what your intent is with respect to "categories" and "options" or if they are related to each other. I don't understand enough about what you're doing to help you further with that, so I'll talk through what I think it might be, and I'll tie in things like adjacency tables at the end.

With respect to adjacency tables, I can say that the generic construct is useful in concisely storing the structure of a tree, particularly if the structure of the tree itself is dynamic. If you have a tree where branches are being added and removed, and may have an unknown number of sub-branches, which may or may not have sub-branches, and so on, then you must use this type of a construct. However, the cost of this approach is that the queries can become quite complex.

In general, you have to figure out what information you are trying to store and how it will be used. What exactly is an "option" as you see it? Is it an attribute (e.g., "color"), or is it an attribute-value pair (color = red)? Is a "model" defined by the fact that it has certain attributes (e.g., has an optional sunroof) or by the fact that it has attributes with certain values (e.g., body_style = coup; suspension = sport; engine = 2.7L T).

My thought is that product categories have certain attributes which discriminate between versions of that product customers might order (i.e. models or SKUs or inventory items or what have you). This is also the information needed for replenishment by the supplier, for the prices used in wholesale and retail transactions, for shipping, etc.. Here are some hypothetical product categories:

vehicles
body style
engine
color
interior
etc.

linens and clothing
fabric
color
size
weight

appliances and furniture
style
finish
size
weight

Now, "models" (or SKUs or "inventory items", or what have you) inherit these attributes, according to their product category, and I think they probably have fixed values for some attributes (the ones that define the model) and have customer-selected "options" for others. So a model's attributes are defined by its product category as it is entered into the system, the attributes whose values define the model are assigned at that time, and some other attributes will not have values (or final values) until an order is placed. Some of the standard attributes for the category may not be applicable, and should be designated as such. So, the data model must be of a generic and flexible nature:

[products]
id
name
fk_category_id

[categories]
id
name

[attributes]
id
name

[category_attributes]
id
fk_category_id
fk_attribute_id

[model]
id
name
product_id

[model_attributes]
id
value <--(e.g., "red", or another fk to an "allowed attribute values" table)
is_customer_option
fk_attribute_id
fk_model_id

An operator would create a new model by picking a product, defining its category, and then populating each of the models definitive attributes with values, then designating other attributes as "customer options" (perhaps with allowable values, which would add another table).

This ties together your product categories and your models and options. It will also perform well. Maybe it doesn't fit your scenario, but it might give you some seeds of thought.

Now, where the adjacency table construct comes in is if you have a complex hierarchy containing important information. Here what we have is a simple two-tier hierarchy of product categories, products and models, inheriting attributes according to a product classification. What if we can't fit our real-world scenario into this data model, and we need, for example, an n-tier product hierarchy?

Well, That's when you'd collapse the "product" and "model" tables into a single "products" table:
[products]
id
name
parent_id

And your "model_attributes" tables becomes a "product_attributes" table:

[product_attributes]
id
value
is_customer_option
fk_attribute_id
fk_product_id

And your queries and user interface logic would have to consolidate the information of each end-item and all its parents each time it's presented or processed.

By the way, a sort of adjacency table is used in the Gentoo portage profiles. The filesystem is the database, but if you look you'll see a "parent" file at every node.
_________________
Naib wrote:
you need a dick sometimes to deal with the assholes
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic    Gentoo Forums Forum Index Off the Wall All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum