Help with Modeling

[dragonwell]

This is probably a "Modeling 101" question, but it seems to have me stumped at the moment.

My question regards a multi-level heirarchy in a configuration system. At the bottom of the chain is a "Product" entity. The Product can be in 1 or more "Category" objects. Furthermore, each Category can be in one or more "Store" objects. The twist is, I must be able to place one or more Products in each Category-Store instance.

Starting with the StoreCategory relation, I have a simple many-to-many joining table

StoreCategory
StoreID
CategoryID

Now to be able to place the products in each of those instances, I have a ProductCategory table

ProductCategory
ProductID
CategoryID
StoreID

What bothers me is that now I have created the relationship between Store and Category twice which creates redundant data. If I give the StoreCategory table a surrogate key as in

StoreCategory
StoreCategoryID
StoreID
CategoryID

could I use that in the ProductCateogry table in place of the StoreID and CategoryID columns?

ProductCategory
ProductID
StoreCategoryID

I'm not sure I like that either.

Can someone help me see where I'm wrong with these relationships?

Thanks in advance.

 

[RiverGuy]

I'm not a DBA, but this is how I would look at it from a programmer's standpoint. I'm sure others may disagree, but I don't look at building systems around a perfect text-book database. I look at building systems with the best all around usuability in mind.

Saying that a product can be in one or more categories doesn't make sense with the examples you have, IMO. This is because if a product was defined as being a "Tool," then that information is going to be duplicated if more than one store carries that product in the tool section.

So, your product-category really doesn't follow a heirarchy, IMO.

The way the heirarchy really seems to work is that Store has Categories, and an object composed of a Store and Category has Products.

So in this case, I think your second example works. I think it would make more sense if you thought of StoreCategory as an object. This may be easier to do in instances where you have other fields in the table creating the M:N relationship. Something like "This is the tool section at the back of the store.

 

[dragonwell]

Yes - StoreCategory is an Object... I guess I'm struggling with what exactly that object is. A Zone? I think Zone works, but to me zone implies a distinct, physical thing. But in this domain, the top level entities share these zones (as they are not really physical objects).

I also appreciate what you say regarding usablity. My main concern is to just create a system that is flexible enough for the problem at hand, and from experience going for as much normalization as is reasonably possible is the way to do so.

 

[RiverGuy]

I think it's all perception. Having never worked for one, and probably simplifying it too much--a Cell Phone Contract seems like a "real" thing. You sign it's represententation, which is a peice of paper. And it's a legal binding agreement. But in a database, you can kind of think of it as a join between a Customer and a Service Plan. The contract will have the Customer ID...along with the Service Plan ID. It may even have it's own Contract ID.

So I still think you are on the right track. But I do think, that it's just sort of a weird situation. Maybe a limition of the RDBMS model.

 

[alvechurchdata]

I must be able to place one or more Products in each Category-Store instance.

I'm confused by the terminology. Are you building a relational model, a hierarchical database, or set of class libraries?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[willir]

Dragonwell (from TSR / Wizards of the Coast??)

First, you are on the right track. I am not sure I can decide for you which model is best.

Use test data to find out which model best suites your case. Enter the test data and then use SQL statements to verify you can retrieve the desired information.

...Moving on
Now let's look at the design.

You seem to be saying...
- A product can be in many stores
- A store can have many products
M:M

- A category can have many stores
- A store can have many categories
M:M

Now I may be about to blunder. I am not quite sure I understand your designs needs wel enough...
- A product can have many categories
- A category can have many products
M:M

Sort of like a daisy chain, each with a many-to-many relationship. The obvious question would be to ask which is the primary?

But perhaps we do not need to define the primary, and just develope forms to present the data from each perspective.

Have a form for Stores and display in two subforms with the Products and Categories information.

Have a form for Products and display in two subforms with the Stores and Categories information.

Have a form for Categories and display two subforms with the Products and Stores information.

Have a mechanism such as a command button or the DoubleClick event procedure to toggle / drill down to the related items. Store <-> Product <-> Category <-> Store

You have two approaches that I can see...

tblStoreCategoryProfile
StoreID - foreign key
CategoryID - foreign key
ProductID - can have a value or can be null

tblProductCategoryProfile
ProductID - foreign key
CategoryID - foreign key
StoreID - can have a value or can be null

BUT, perhaps a better approach, if I understand your needs, you can use a 3 classic M:M tables may suit your needs...

tblStoreCategoryProfile
StoreID - foreign key
CategoryID - foreign key

Primary key = StoreID + CategoryID

tblProductCategoryProfile
ProductID - foreign key
CategoryID - foreign key

Primary key = ProductID + CategoryID

tblProductCategoryProfile
StoreID - foreign key
ProductID - foreign key

Primary key = StoreID + ProductID

I must be able to place one or more Products in each Category-Store instance

Although you can have many combinations, you do not have to use them all.

Having said all this, I am not as confident as I normally am in suggesting a design. Test the design with data.

 

[sillysod]

Im confused about two things, in that i can see 2 interpretations of a "product"

In that its either what i would call a "part" or a "lot"

A part would be the type of product ie in a catalogue you would list all your parts as your products.

A lot is a particular "instance" (for want of a better word) of that product in stock

From your explanation i think you are talking about the second option

So looking at it from a "lot" point of view

Each lot in stock is a particular "Product" and you can have many lots in stock against each of your products

Then you have areas that can hold stock (this is your hierarchy)

Now you need to be able to define stock areas and then yards within that area then bins within that yard etc etc down to potentially an infite number of levels

So when you recieve goods you book it into stock assigning it its correct Product code (against the product code you have all the "header" information such as name of product and more importantly product type

You could easily create a link table so that you have a many to many relation for products against product types

Then you select a stock location from your hirearchy and create a stock reciept in your stock transactions table which has a "location" field that links to a stock location.



Right having read over this ive either gone way off the mark or hit nail on the head. But i dont think ive explained it too well. Give me a few mins an i will upload an example to my webby

 

[dragonwell]

OK, thanks for all that. That's a lot for me to think about, and the answer may be above. But I thought if I used a more familiar situation as an example it might be easier to see. Consider this:

Offices -- Users -- Permissions

A User can be in one or more Offices, with a different set of Permissions for each Office.

tblOffice
OfficeID
Name

tblUser
UserID
Name

tblPermission
PermissionID
Name

tblUserInOffice
UserID
OfficeID
PermissionID

A little easier to comprehend than the products-stores-categories example, but essentially the same types of relationships. The last table is like a classic M:M joining table, except that it defines a three-way relationship. I guess it boils down to a M:M:M relation. The problem I see is that if a User had more than 1 Permission for an Office, it would appear that they are "in" that Office more than once. Would that work? Or do I need another table to relate the Permissions to each User-In-Office Instance - i.e. another M:M table.

 

[willir]

So you are saying that a users "profile" (permissions / security) will depend on the location they login from. In order to apply the proper "permissions", you need to know a) who the user is and b) their location. Is this correct?

If so, then, my initial thought is that "permissions" are tied to the UserAccount + Location, or in your example, UserID + OfficeID. This would suggest that perhaps you do not have a M:M:M example.

Followig this through, it seems to me that although you can have a M:M for User and Offices, it may not be correct to assume that all users will have different permissions for all offices. Rather, it may more sensible to have several defined permission "profiles" plus a default profile to be used if no matches are found for user + office -- OR -- not allow any permissions to be applied if no match is found. Regardless, permissions are applied to specific User + Office...

tblOffice
OfficeID - pk
OfficeName

tblUser
UserID - pk
UserName

tblPermission
PermissionID - pk
PermissionName

tblUserPermissions
UserID - fk
OfficeID - fk
PermissionID - fk

Primary key = UserID + OfficeID + PermissionID

Discussion:
If permissions are explicit, then they have to be defeined for each UserID + OfficeID -- No matches would mean no permissions.

Or, you can create a Defualt "office" location which would be used if no matches are found for UsrerID + OfficeID. Note that the "Default" could include "no access" as well as other specific permissions.

Plugging in some data...

[tt]
tblOffice
OfficeID OfficeName
1 Corporate
2 Region-NW
3 Region-NE
4 Region-SW
5 Region-SE
6 Default

tblUser
UserID UserName
1 John Smith
2 Jan Swartz
3 Cindy Lee
4 Casey Jones
5 Mary Poppins

tblPermission
PermissionID PermissionName
1 Full admin access
2 Admin-Corp
3 Admin-NW
4 Admin-NE
5 Admin-SW
6 Admin-SE
7 Normal
8 Guest

tblUserPermissions
UserID OfficeID PermissionID Comments
1 6 1 Smith, Default, full admin
2 1 1 Swartz, Corp, full admin
2 6 7 Swartz, Default, normal
3 2 3 Lee, NW, NW admin
3 3 4 Lee, NE, NE admin
3 6 7 Lee, Default, normal
4 4 5 Jones, SW, SW Admin
4 5 6 Jones, SE, SE Admin
4 6 7 Jones, Default, normal
5 1 8 Poppins, Corp, Guest
[/tt]

Assuming your new model is correct -- Store, Category, Product is the same as Office, the above Users and Permission should give you a better idea on how to handle your situations.

Richard

 

[dragonwell]

Thank you, Richard. That makes sense to put the permissions into "Role" groups (profiles), in this case. But I don't know if it would work for a true M:M:M model. I'll think about it though...

Thanks also to everyone for contributing.

 

[dragonwell]

After some googling, it turns out there is a name for this ;-) I figured there was... Anyways it's called a ternary relationship. Here is one good example:

http://www.razertech.com/blueprints/database/star/stars-2.htm

and here is another

http://www.chillisource.com/dds/tutorial/TutorialTernary_Relationships.htm