Databasedesign problem price list system

[heinzel]

Hi All,

After searching the web and this specific forum I can't seem to find a suitable solution to my problem and after hours of thinking I wasn't able to come up with a solution that will fit the problem.

This is the case:

I need a database model that will support a price list system for determining the price for a given article with there specific properties and suppliers. The article group defines the set of properties for there underlying articles.

Here is my biggest problem.

Each article group contains a other set of properties. The properties are the key to determine the price of a article within a price list containing a pace-list.

For example:

Article group: Booklets (properties: recto (=Side of color),verso (=Side of color),pages)
article: ND Booklet

The ND booklet can have multiple configurations (properties) like:
Config 1:
Recto = 4
Verso = 4
Pages = 2

In this configuration the ND booklet pricing is as follows:
For 1000 units has the price per unit = 0,2345
For 2000 units has the price per unit = 0,2212
And so on

Config 2:
Recto = 4
Verso = 0
Pages = 4

In this configuration the ND booklet pricing is as follows:
For 1000 units has the price per unit = 0,2234
For 2000 units has the price per unit = 0,2045
And so on

Article group: D-Box (properties: Thickness)
article: D-box small

Config 1:
thickness = 30mm

In this configuration the D-box small pricing is as follows:
For 1000 units has the price per unit = 0,1223
For 2000 units has the price per unit = 0,1089
And so on

Config 2:
Thickness = 50mm

In this configuration the D-box small pricing is as follows:
For 1000 units has the price per unit = 0,1456
For 2000 units has the price per unit = 0,1224
And so on

The tables I could come up with are these:

Base table for the article groups.
Tbl_article_group
Articlegroup_id
Articlegroup_description
…

Base table for the different properties
Tbl_property
Property_id
Property_description
Property_data_type

Linking table for defining article groups to specific properties
tbl_articlegroup_properties
agp_id
fk_articlegroup_id
fk_property_id

Linking table for defining article to specific properties
tbl_article_properties
ap_id
fk_article_id
fk_property_id
property_value

Base table for articles
Tbl_article
Article_id
Article_description
Fk_articlegroup_id

Base table for supplier
Tbl_supplier
Supplier_id
Supplier_name

Price list table that contains the prices per supplier, per article, per pace qty
Tbl_price_list
Pricelist_id
Fk_supplier_id
Fk_article_id
Pace_qty
price

The properties table for article groups serves as template for setting up an article of the chosen article group.

So my question is how to link the specific article withi a specific configuration to the price list.

An alternative (easy way) to the design above is to setup a table with a predifined set of property columns Like cPrp1,cPrp2,cPrp3,cPrp4,cPrp5.
But I really like to avoid this kind of solution because of the inefficient way of hard disk space that will be wasted for field that aren’t being used..

I’m also having problems retrieving an the correct set of articles when I query the properties for a article. (Not really a database design problem but rather a how to query question)

Example:

Ap_id	Fk_article_id	Fk_property_id	Value
1	1	RECTO	4
2	1	VERSO	4
3	1	PAGES	2
4	2	RECTO	4
5	2	VERSO	4
6	2	PAGES	2
7	3	RECTO	4
8	3	VERSO	0
9	3	PAGES	2
10	4	RECTO	4
11	4	VERSO	0
12	4	PAGES	2

The statement I tried to use to get the articles that have a recto 4 ,verso 4 and pages 2 .

This gives me all the article where 1 of the criteria in the where statement is true.

SELECT * 
FROM tbl_article 
WHERE article_id IN(SELECT fk_article_id FROM tbl_article_property 
 			WHERE  (fk_property_id = ‘RECTO’ AND value = ‘4’) OR
				    (fk_property_id = ‘VERSO’ AND value = ‘4’) OR
                  			    (fk_property_id = ‘PAGES’ AND value = ‘2’) )

This gives me no results because all the criteria concerns 1 row.

SELECT * 
FROM tbl_article 
WHERE article_id IN(SELECT fk_article_id FROM tbl_article_property 
 			WHERE  (fk_property_id = ‘RECTO’ AND value = ‘4’) AND
				    (fk_property_id = ‘VERSO’ AND value = ‘4’) AND
                  			    (fk_property_id = ‘PAGES’ AND value = ‘2’) )

So how do I construct a statement on both three properties wich will give me only the those articles who fit the whole requirement?

I tried to be as clear as possible. So I hope it’s clear to what I’m trying to accomplish.

[debasisdas]

may be i will read this LONG question in weekends.

[heinzel]

may be i will read this LONG question in weekends.

Thanks for your response.

But I already thought of another approach that will help me to achieve my goal.
So I will leave this thread for what it is and close it.