Very different products and how to control the data

[stonward]

Help!

I have a database with a large number of (very similar) products - tyres - but in addition I need to add other completely dissimilar products/services. At the moment I have a table for Tyres (Products) and another two tables, one for Batteries and the other, Non_Tyre_Products. These are happily linked to a categories table and I thought all would be fine....

Until, that is, I came to building a point of sale form. I have to cater for any combination of products/services. My point of sale forms subform is primarily based upon SaleDetails, but at the moment the form comes up BLANK.

Can anyone see a better way of organising this (the relationships between tables has worked fine)...in particular, can anyone see how a POS might be constructed? Have you seen this sort of problem before?

Thanx,

Stonward

[stonward]

Okay, I'm gonna think aloud...and try and give everyone more info on my problem..

I think I need another 'layer' somehow, to decipher what product is being 'bought' and then to run the required SQl/enable the right fields in the subform.

But the practicalities of this are out on the outer edge of my abilities!

Stonward

[FishVal]

Hi, Stonward.

As far as I've got it you have 3 different tables to store products of 3 different categories. The reason is that different category products require different information to be stored. However some of the product fields should be the same: e.g. ID, Name etc. Could you post the structure of these 3 tables mentioned?

I have inventory db where more or less the similar situation is resolved in the following way.

Tables:

tblItemCategories
keyCategoryID PK
txtCategoryName

tblProperties
keyPropertyID PK
txtPropertyName

tblCategoryProperties
keyCategoryPropertyID PK
keyCategoryID FK
keyPropertyID FK

tblItemNames
keyItemNameID PK
keyCategoryID FK
txtItemName

tblItemProperties
keyItemPropertyID PK
keyItemNameID FK
keyPropertyID FK
txtPropertyValue

[stonward]

FishVal,

Thanks for your reply....
Your example still has me a little baffled - I can see what you're doing, breaking down the data even further, but I can't see how this will help me, say, make a point of sale form/subform or the queries it'll be based on. Here are my product table details:

tbl_Tyres;
ProductID - PK (auto)
Handle
Category
Series
Size
Rating
Manufacturer
Notes
CostEach
Trade
Retail
Diameter
Width
Stocklevel

'TYRES' makes up 98% or more of the use of the system...

Tbl_Batteries
ProductID - PK (auto)
Category (always '16' - batteries)
Reference (Manufacturers Ref No)
CostEach
Retail
Notes
StockLevel

tbl_NonTyreProducts

ID - PK (auto)
Category
Product
UnitCost
TradePrice
RetailPrice
Notes

I can see how I could easily narrow it to two Products tables, but I can't see how this would help? I can also see how i can make a form with a combo (say) that when a cetain category is selected, the fields alter to suit. My problem is mainly with building the queries/SQL strings necessary....they come up 'empty' until I remove (say) the batteries table...Help Please!

Stonward the Weeping

[FishVal]

FishVal,

Thanks for your reply....
Your example still has me a little baffled - I can see what you're doing, breaking down the data even further, but I can't see how this will help me, say, make a point of sale form/subform or the queries it'll be based on. Here are my product table details:

tbl_Tyres;
ProductID - PK (auto)
Handle
Category
Series
Size
Rating
Manufacturer
Notes
CostEach
Trade
Retail
Diameter
Width
Stocklevel

'TYRES' makes up 98% or more of the use of the system...

Tbl_Batteries
ProductID - PK (auto)
Category (always '16' - batteries)
Reference (Manufacturers Ref No)
CostEach
Retail
Notes
StockLevel

tbl_NonTyreProducts

ID - PK (auto)
Category
Product
UnitCost
TradePrice
RetailPrice
Notes

I can see how I could easily narrow it to two Products tables, but I can't see how this would help? I can also see how i can make a form with a combo (say) that when a cetain category is selected, the fields alter to suit. My problem is mainly with building the queries/SQL strings necessary....they come up 'empty' until I remove (say) the batteries table...Help Please!

Stonward the Weeping

Hi, Stonward.

To my mind, you need to make a 4th table (let us say [tblProducts]) where the following fields supposed to be ProductID, Category, <all fields needed for "SalesForm" proper calcilations (price etc.)>.

The rest is your decision:
1) you are going with the solution from post#3 having a flexible db ready to deal with any product category / fields combination
2) you add FK associated with the tblProducts.ProductID to your 3 tables each dedicated to particular product category

Anyway this is a very general issue - the internal ideology of your db.
Without understanding your particular situation I can easily suggest you a not optimal or completely unsuitable tables relationship schema.
You should better than any other understand what functionality your db should have to fit all present and possible requirements. So to make my advices more useful you have to describe the whole situation in a very detailed manner.

P.S. The structure of tables you've provided seems me somewhat useless so far. Did you mean each product has a constant price, size etc?

[stonward]

FishVal,

I'm not sure how I'd put across to you my system as it is in detail....

There are about 1200 products in Tyres at present, which is growing steadily at about 10 per week. Do I not put in prices in the products tables? The other fields stay stable for each individual tyre...Im not sure what you're getting at.

I'm going to continue battling it - I am intrigued by your idea of adding a table (although I do already have a nice set of one to many relationships between all my tables). Can you explain more on this?

In the meantime, I'll try and find a way to simplify my requirements.

Thanx for your patience,

Stonward