just need your help 1

[ebu]

just a theoretical problem:
consider set of objects X. Every object X has its set of properties {x1, ..., xn, Xiy1,...Xnyn} where x1...xn - common set of properties for all objets.
The question:
how tables should be organized in a relational databese to represent set of object X in most effecive way.

 

[svanels]

????? S. van Els
SAvanEls@cq-link.sr

 

[carp]

I'm not sure what you're trying to convey with "Xiy1,...Xnyn", but for x1..xn you would create a table with one column each for attributes x1..xn. You might also need an extra column for a primary key value (if none of the x1..xn columns will suffice). In this case, you would have n+1 columns (PK + x1..xn).

I think this is about as explicit as you can get with such a generic question.

 

[ebu]

yes, senx all very much.
and probably sorry for bad question and bad enlish.
So, the problem is:
what am i to do, if all opbjects (some goods for example) have set of common properties (price, weight, color and so on) and each object has its own generic properties which also shuold be present in database (for printers - printing speed, for disks - capasity ...).
The first thing which comes in mind - create one big table

like this (columns):
create table list ...
1. id int not null auto_increment, primary index (id),
2. category set ('disks', 'printers'...),
3... so on...

and then tables for each kind of objects
create tbale disks
1. id - which should be equal to items id in big table ...
2. ... generic properties.

here the problem is that i should maintain ties beetwin id in big-list-table and ids in all sub-tables...

so, what do you think?

 

[svanels]

Breaking up in categories is a good thing, but can be difficult. Define your categories good. Hold the master table as small as possible.
Remember not all properties you must, or can put in just one field.
Use memofields for generic descriptions

Regards S. van Els
SAvanEls@cq-link.sr

 

[MikeJones]

If I understand your problem correctly you could have a table holding "Soft Attributes" i.e. attributes that change accourding to what type of "thing" your storing info about. In your example printing speed and disk capacity.

You would need some extra tables

You would still have your main table with 1 row for every object. This would hold all values common to all objects as Carp says above.

Another table would then hold categories of soft attributes, an object would fall into exactly 1 category.

OBJECT_CATS

Cat_id NUMBER,
description VARCHAR2(4000)

Another table would hold Attributes that are valid for a category

OBJECT_CAT_ATTS

cat_cat_id NUMBER,
att_id NUMBER
description VARCHAR2(4000)

Another table would hold attribute values for an attriburte (where applicable) if you only wanted the user to select 1 of a number of possible values from a pop list.

OBJECT_CAT_ATT_VALS

cat_cat_id NUMBER,
att_att_id NUMBER,
Value VARCHAR2(255),
description VARCAHR2(4000)

Finally another table associates these to an object

OBJECT_CAT_ATT_VAL_US

pk_id_from_main_table NUMBER
cat_cat_id NUMBER,
att_att_id NUMBER,
value NUMBER

When you create an object the user must select which categoty it falls into which inturn defines which soft attributes are valid for it.

HTH,

Mike.