A question about database design

[AdamOnAccess]

I have an odd relationship and I would like to get some opinions on the best way to handle it.

3 tables:
1. WAREHOUSES.
2. PROD_CATEGORIES (One in One-to-many with PRODUCTS)
3. PRODUCTS (Many in One-to-many with PROD_CATEGORIES )

Usually, about 100 products are assigned to each category.

There are over 1000 warehouses. When allocating products to warehouses, typically you would assign the product category to the warehouse and all the products in that category would be stored in the assigned warehouse. Occasionally, there are exceptions: A product category will be assigned to a warehouse, and 3 or 4 individual products will be excluded and not stored in the warehouse.

When designing the database, is it better to create the Many-to-many relationship between Warehouse-Products, or is it better to make it between Warehouse-Prod_Categories and maintain another Many-to-many relationship between Warehouse-Product_Excepti ons.

The former will be simpler to store and easier to make queries, but will bloat the database. The later will be more complicated to store and query, but it will require much less storage.

Only one guy maintains this database and he is fair at writing queries.

What do you think is the best way to set up these tables?

Thanks,
Adam

[NeoPa]

With what you describe, a simple One-to-Many between WAREHOUSES & PROD_CATEGORIES is not only not adequate, it is not possible. If it were set up, then you could have no exceptions.

You could define some basic defaults if you like, such as would link a PROD_CATEGORIES item with a particular warehouse, but this would have to be handled separately. A relationship between WAREHOUSES and PRODUCTS however, would be required.

I hope this helps.

[AdamOnAccess]

Hi NeoPa,

Thanks for the reply. Yes, I'm aware the Many-to-one in warehouse to product category will not make it alone. I'm trying to decide between these 2 alternatives:

1st Choice:
Warehouses > Many-to-many < Product Categories
And
Warehouses> Many-to-many < Product EXCEPTIONS

or 2nd Choice:
Warehouse > Many-to-many < Products

The first choice will not bloat the many-to-many relationship tables, but it will be more complicated to maintain.

The second choice will be easier to maintain but it will lead to a huge many-to-many table and I'm concerned that it will slow the whole application down.

I guess my question boils down to this: What will make access run more smoothly: 1) smaller amounts of data in 2 tables and complicated queries, or 2) a much larger amount of data in a single table and less complicated queries?

Sorry if this is jumbled. Thanks for the help.
Adam

[NeoPa]

Adam,

From your first paragraph it seems clear you have not grasped what I was saying. Please read through my last post again carefully. There seems little point in continuing the discussion from a position behind where we have already reached.

If there's anything you cannot understand then please let me know specifically. I'm happy to continue, but simply see no point in going over the same ground again from scratch.

[AdamOnAccess]

Hi Neopa,

I've read over your post again - a few times just to be certain - but I think I haven't communicated this properly. You said...

With what you describe, a simple One-to-Many between WAREHOUSES & PROD_CATEGORIES is not only not adequate, it is not possible....

"
If you look at the relationships in my second post, Choice 1, you'll see that I'm suggesting a MANY-TO-MANY relationship between warehouse and product category, not One-to-Many.

...If it were set up, then you could have no exceptions.

If a Many-To-Many relationship is set up between Warehouse and Product category, you could have exceptions by keeping a second Many-to-Many relationship between Warehouse and Products that are not included in a specific warehouse. Under the stucture described in choice 1, a query to show all the products in warehouse 2 by category, except product 2, would look something like this:

(I've tested this query and it does work. If you would like a copy of the file, let me know.)

SELECT Products.ProductName, ProductCategories.ProductCategory, Warehouses.Warehouse
FROM Warehouses 

INNER JOIN ((ProductCategories INNER JOIN Products ON ProductCategories.ProductCategoryID = Products.ProductCategoryID) 

INNER JOIN WareMMCategory ON ProductCategories.ProductCategoryID = WareMMCategory.ProductCategoryID)

ON Warehouses.WarehouseID = WareMMCategory.WarehouseID

WHERE (((WareMMCategory.WarehouseID)=2) 
AND ((Products.ProductID) 
Not In (Select ProductID From WareMMExceptProducts WHERE WarehouseID=2)));

In the rest of your post, you said:

You could define some basic defaults if you like, such as would link a PROD_CATEGORIES item with a particular warehouse, but this would have to be handled separately. A relationship between WAREHOUSES and PRODUCTS however, would be required.

But this isn't so. The table structure I defined above would allow you to link Warehouses to Product Categories

Admittedly, this part I don't understand, but I think it is related to the assumption that I am linking Warehouse and product categories on a 1-to-many, and that is not my intention.

If I'm missing your point here, I'm genuinely sorry. You've helped me before and I certainly don't want to look like some pest, but I think I didn't make my original posts clear.

If I've done a better job of communicating what I'm doing this time and you understand what I'm doing, then I would like to rephrase my original question and ask again:

What will make access run more smoothly:
1) smaller amounts of data in a pair many-to-many tables and complicated queries
or
2)a much larger amount of data in a single many-to-many table and less complicated queries?

Again, if I'm missing something and you don't want to take this further, just let me know and I'll drop it.

Thanks again,
Adam

[NeoPa]

Well, it seems it is I that should apologise to you. I did indeed misunderstand what you wrote, and it was I that skimmed too quickly over without properly comprehending your point.

I'm in a rush now so I hope to come back to this later (when I will add the code tags that you missed and drop a comment to that effect so you remember them in future :D). I will give it all a proper read and give more consideration to what an appropriate design may be for you.

Appologies again till later.

[AdamOnAccess]

No apologies necessary. Your help is always appreciated.

I did look for the proper tags but didn't find them. I used QUOTE because I'm familiar with that one. If you leave me a link to the proper tags, I'll save it.

By the way, is there some easy way to post images in this forum? I was thinking that maybe I could copy the screen showing the Access relationship window and post it. I don't think it would be necessary on this question now, but it would be good to know if it's possible.

Thanks again,
Adam

[NeoPa]

I dug this up :

Tags (generally) are done as matching pairs where the opening one is surrounded by [...] and the closing one by [/...]. A set of buttons is available for ease of use in the Standard Editor (Not the Basic Editor). The one for the [CODE] tags has a hash (#) on it. You can choose which editor to use in your Profile Options (Look near the bottom of the page). You must select the code in your post, and hit the button on the tool bar which looks like a hash (#). This will automatically format the post such that the [CODE] tags surround the code you're posting. This will display the code in a Code Box, quite differently from the standard formatting of a post.

[NeoPa]

For posting images, I believe JPEGs are always usable, but other formats may be too.

When posting (or editing in the Advanced page) there is a button lower down the page titled Manage Attachments. This should give you all you need for attaching images.

[AdamOnAccess]

Hi Neopa,

Ok, I've uploaded an image of the relationship window so you can see what I'm unsure about. The relationship is unusual because when a warehouse stocks a product category, it typically takes on all products in the category. Only a handful of products in a category are exceptions and not stocked.

It seems to make the most sense to design the database for a many-to-many between wareshouses and product categories, and a second many-to-many between warehouses and product exceptions will keep track of the rare exceptions (as the image shows).

But as you can see, the relationship is complicated and I'll have to run complicated queries that might slow the whole system down.

The alternative would be to just keep a many-to-many relationship between warehouses and products, but that will create a bloated many-to-many table and that could slow the whole system down.

Considering Access's capabilities, which is the best way to go?
Thanks,
Adam

[AdamOnAccess]

If anyone out there can offer some advice on this question, feel free to chime in.
Thanks,
Adam

[NeoPa]

I've been a bit slack recently Adam. I'll try to look at this again properly sometime soon.

[NeoPa]

It appears there is much about this question I didn't grasp correctly when first I read it. I can see now that products can be held at various warehouses, which is the normal way of things I suppose. I simply misread you first post as indicating it was one-to-many. My mistake.

The Many-to-Many relationship here is essentially a Stock table, containing data relevant to each instance of a product being stocked at a warehouse. This makes absolute sense and is generally more than a simple relationship table (maintaining stock levels as well etc).

As for your Product Group relationship table, I'm not so sure exactly what you expect to gain from this. If it is for determining the best default warehouse to assume when creating a new product, then this can work. Do you want each of a potential many associations set up? If so, how would the interface work? How would individual records be excluded if the operator decided not all warehouses were actually appropriate for this particular (new) product? I can't answer these questions, but from what you've posted already it seems likely you have already given them consideration. If not then it may help to.

If, on the other hand, you only want to default a single warehouse on creation of a new product, then a one-to-many link would be preferable, and the software could allow the operator to accept the default or specify a new one explicitly.

As for performance, I don't have any figures, but I would say with confidence that a one-to-many relationship would have better performance than a many-to-many. By how much is another matter. I would expect Access could handle either reasonably well. You could probably tell the difference if you had enough records, but I doubt it would shout at you, and for smaller numbers of records it's possible it wouldn't even be noticeable. That's all without any testing of course. Just what I imagine it would do from my limited experience of such things.

I'm conscious I've not been a great deal of help here Adam, but I've rarely needed to go into this sort of territory. Best of luck with your project :)

[AdamOnAccess]

Hi Neopa,

Thanks for the reply. I'm sorry you feel you haven't been much help. Just having a pro look over the idea and give some feedback is plenty of help.

Here's a typical situation:

The user will be told to assign a specific product category to a specific warehouse. Within a category, there can be a few hundred products, so it is much easier to assign a single product category to a warehouse instead of hundreds of individual products.

Later, the user will be told not to stock 3 or 4 products from that same category in the warehouse (again, that category can contain hundreds of products and we only want to exclude 3 or 4). So now he will simply "check off" the specific products in that category that will no longer be stocked.

If I were to set up the database without the product category table and make a many-to-many between warehouses and each individual product, everytime we want to store a category in a warehouse, the relationship table will require one record for each product and that means hundreds of records in the table. With multiple warehouses, that relationship table will quickly have thousands of records.

But if I set up the database as I described, the relationship table between warehouse and category requires only one record, and the relationship table between warehouse and exceptions will only require four or five records.

In essence, I'll be storing much less data in the tables, but I'll need more complicated queries.

If you had a situation like this, which way would you choose to go:

1. Go with storing many more records in a less complicated set up?

or

2. Go with storing very few records in a more complicated set up?

Thanks,
Adam

[NeoPa]

Before I get to that may I ask you to explain something for me. From you first post, and apparently confirmed in your last (#14), it seems that items (both Product and Product Categories) are associated with only a single Warehouse each. Maybe I'm misreading this, but if not, I see no reason for the links to be Many-to-Many at all (hence my initial misunderstandin g - It made no sense to me that anything but One-to-Many relationships would be suggested). I need to understand this better before I can give it proper thought.