By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,560 Members | 1,093 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,560 IT Pros & Developers. It's quick & easy.

database design (handling multiple units and properties)

P: n/a
I am designing a database to handle different kinds of products ... and
these products can have different properties...for example, a trouser
can be specified by the width, length, color, and other additional
properties...while you might need only one property for another
product, e.g litres for a soft drink...so I was thinking of creating a
properties table, a units table and a product table, properties-unit
table...
and products-properties table, the properties table has entries like
"Weight, length, Color...." and so on, and the unit table has entries
like "gm, Kg..." and so on...and properties-unit table associates which
unit can be used for which property like "Weight:gm, Weight:kg,
Length:cm..." and in the products-properties table I will have
something like "Jeans1:width=32, length=34" ...i.e for each property of
the product I will have a row and so on....

1)do you think this is a nice design? what can go wrong? the fact that
I have one row for each property of a product makes it really hard to
specify the price of the product for example

2)It confuses me how to handle different units... first I thought of
storing everything in metric unit and then do some conversion for the
user interface...but consider things like shoes and so on, where there
are more than two units ... US, european, british etc.... how do you
think is the best way to handle multiple units for a given product?

Jan 9 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
1 - You've got a bit of a conundrum, as some products are sold by unit
(i.e. soft drinks) and some by product (i.e. trousers are usually one
price regardless of length). Therefore, you're going to need a table
which lists products by barcode or some other distinguishing ID.

All those ancillary tables (property, units, etc...) are nice if
you're going to have dropdown boxes on the Product Entry form, but are
otherwise useless as all those specs should be entered in the Product
table if they drive the price.

2 - Pick one unit to work with, and keep a Conversion table that can do
the conversions on the fly (for use in reports).

beatdream wrote:
I am designing a database to handle different kinds of products ... and
these products can have different properties...for example, a trouser
can be specified by the width, length, color, and other additional
properties...while you might need only one property for another
product, e.g litres for a soft drink...so I was thinking of creating a
properties table, a units table and a product table, properties-unit
table...
and products-properties table, the properties table has entries like
"Weight, length, Color...." and so on, and the unit table has entries
like "gm, Kg..." and so on...and properties-unit table associates which
unit can be used for which property like "Weight:gm, Weight:kg,
Length:cm..." and in the products-properties table I will have
something like "Jeans1:width=32, length=34" ...i.e for each property of
the product I will have a row and so on....

1)do you think this is a nice design? what can go wrong? the fact that
I have one row for each property of a product makes it really hard to
specify the price of the product for example

2)It confuses me how to handle different units... first I thought of
storing everything in metric unit and then do some conversion for the
user interface...but consider things like shoes and so on, where there
are more than two units ... US, european, british etc.... how do you
think is the best way to handle multiple units for a given product?
Jan 9 '07 #2

P: n/a
Not sure there's a one-size-fits-all answer to this question. :-)

The first distinction is to identify the unit of measurement applicable to
the product, as distinct from the product descriptor. If you are selling
bulk fuel, you are selling by the litre/gallon, so that's the unit of
measurement. But if you are selling soft drinks, the unit of measurement is
Each: you won't accept it if I offer to buy 0.13 litres of Pepsi, i.e. I
have to buy a whole number of units. From the database perspective, the
quantity in the Pepsi can/bottle is merely a product descriptor, and you are
not going to query the total number of litres of Pepsi sold in a period.

For properties that are merely the product descriptor, you may be able to
get away with just incluing them in the product name. It may be unnecessary
to create different property types (color, size, ...), subtypes (e.g.
different size measurement groupings such as XL,L,S,... verses 6,8,10, ...),
and values.

A clue here might be to look at what your suppliers sell you. In most cases,
its just a Quantity of a Product. (The product may be a pack-size of another
product, but essentially that's it.) From there, the question is:
What do you need to be able to query?

Not sure that's clear, but hopefully it's enough to help you think about
what you really need, and how to keep it as simple as possible.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"beatdream" <Be*******@gmail.comwrote in message
news:11**********************@51g2000cwl.googlegro ups.com...
>I am designing a database to handle different kinds of products ... and
these products can have different properties...for example, a trouser
can be specified by the width, length, color, and other additional
properties...while you might need only one property for another
product, e.g litres for a soft drink...so I was thinking of creating a
properties table, a units table and a product table, properties-unit
table...
and products-properties table, the properties table has entries like
"Weight, length, Color...." and so on, and the unit table has entries
like "gm, Kg..." and so on...and properties-unit table associates which
unit can be used for which property like "Weight:gm, Weight:kg,
Length:cm..." and in the products-properties table I will have
something like "Jeans1:width=32, length=34" ...i.e for each property of
the product I will have a row and so on....

1)do you think this is a nice design? what can go wrong? the fact that
I have one row for each property of a product makes it really hard to
specify the price of the product for example

2)It confuses me how to handle different units... first I thought of
storing everything in metric unit and then do some conversion for the
user interface...but consider things like shoes and so on, where there
are more than two units ... US, european, british etc.... how do you
think is the best way to handle multiple units for a given product?
Jan 10 '07 #3

P: n/a
I second the concept of adopting one set of measurements, eg
imerial/US, and having a conversion table for the rest.

Not quite sure what you are suggesting with the 'product properties'
table. I would avoid creating a'product properties' table with
composite unique index on two fields ie
'product' and 'property name'.

Instead, I would have a table with unique index on just 'product' with
a dedicated column for each property ie column names like 'colour',
'length' etc. I would also have, for example, a 'colour' table of
permitted colours with a unique index on 'colour'. Thus I could
define a foreign key from the product table to the colour table and
enforce integrity. Data integrity on numeric property columns could
possibly be enforced using column constraints.

This would result in a 'product' table with columns that
might need to admit null. However, it will straight forward to write
queries for, say, 'red' size '4' 'shoes'.

If you implement a 'product properities' table with
composite unique index on 'product' and 'property name' there will
likely be a column for 'property value'. This column may need
to accept a mix of 'red', '4' etc so data integrity can not be applied.
This is very bad news. Think also of how much more
complex a query to select items by specifying, say, 5 properites
becomes. Maybe you know how to do the self joins involved but will you
be the only person writing the queries?

I appreciate Access does not have system catalog 'tables' as such. If
it did, it would be clear that the property name was being held
multiple times in the 'product properties' table, instead of just once,
as a column name, in the system catalog 'columns' table.

I know many people disagree with me on this. The 'product property'
style means new 'properties' can be added with no need to add a
database column. The conversion unit solution may seem a little
neater in the
'product property' style implement. However, if I had to, I would
write code to enable users to add columns to the 'product' table
dynamically
rather than use the
'product property' database design style. I am aware that others would
not go so far and wish you well which ever route you choose.

Jan 11 '07 #4

P: n/a
Thank you all for the nice ideas.... I still havenot decided on what to
do exactly...but incase anybody knows... how do big supermarkets and
chains handle their products because they seem to handle so many
different kinds of products, which is basically what I am facing....
handling huge categories of products that dont have common
properties.....
breadon wrote:
I second the concept of adopting one set of measurements, eg
imerial/US, and having a conversion table for the rest.

Not quite sure what you are suggesting with the 'product properties'
table. I would avoid creating a'product properties' table with
composite unique index on two fields ie
'product' and 'property name'.

Instead, I would have a table with unique index on just 'product' with
a dedicated column for each property ie column names like 'colour',
'length' etc. I would also have, for example, a 'colour' table of
permitted colours with a unique index on 'colour'. Thus I could
define a foreign key from the product table to the colour table and
enforce integrity. Data integrity on numeric property columns could
possibly be enforced using column constraints.

This would result in a 'product' table with columns that
might need to admit null. However, it will straight forward to write
queries for, say, 'red' size '4' 'shoes'.

If you implement a 'product properities' table with
composite unique index on 'product' and 'property name' there will
likely be a column for 'property value'. This column may need
to accept a mix of 'red', '4' etc so data integrity can not be applied.
This is very bad news. Think also of how much more
complex a query to select items by specifying, say, 5 properites
becomes. Maybe you know how to do the self joins involved but will you
be the only person writing the queries?

I appreciate Access does not have system catalog 'tables' as such. If
it did, it would be clear that the property name was being held
multiple times in the 'product properties' table, instead of just once,
as a column name, in the system catalog 'columns' table.

I know many people disagree with me on this. The 'product property'
style means new 'properties' can be added with no need to add a
database column. The conversion unit solution may seem a little
neater in the
'product property' style implement. However, if I had to, I would
write code to enable users to add columns to the 'product' table
dynamically
rather than use the
'product property' database design style. I am aware that others would
not go so far and wish you well which ever route you choose.
Jan 20 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.