Hi! I just found this site and am loving it so i'm gonna post my first question.. Hope somebody helps..

I'm using PHP and MySQL.. But That doesn't matter in my DB design..

I'm trying to do the following..

I have several tables in my DB.. Which I want to relate in undefined size groups which I will call PROPERTIES

Example:

TABLES
cars
houses
lots
boats

In each table, there's specific info of each kind..

I want to relate everything in a groups of properties of one guy.. But each guy may have several cars or none; the same with others..

So I thought of this..

Create a Table called PROPERTIES which has these fields..
PROP_id | guy | status

And another table called ELEMENTS
ELEM_id | PROP_id | element | type | description

So then I can add an undefined number of elements to a group..

So if I wan to add a car registered in CARS table with id 35 to a group of PROPERTIES with id 5; I write in ELEMENTS these values..

ELEM_id | PROP_id | element_id | type | description
1 | 5 | 35 | cars | blah blah blah

So that I can get the element_id in the type table (CARS) corresponding to the element...


Is this and efficiente way to do it? Would Anyone use another more direct solution??

I'm worried about the element_id and type fields.. I don't feel it's a "clean" way to relate a field to a specified table..


P.S. Hope someone reads this.. XD

no

you've mixed two types of things -- concrete things, like guys and boats, and abstract things, like properties and elements and types (oh my)

i have no idea what you're trying to do, but "relating a field to a specified table" is probably ~not~ the right thing

^^ Thanks for reading it.. But I couldn't find a better example to explain it..

What I'm really relating is several stuff, which is divided into tables of each kind of item, to events..

Each event has a collection of items from the other tables.. But the number of items can either be one or a hundred and it can be just one kind of item or of all items..

Anyone got some ideas??

Let me rearange it..

I have these Tables..

Kind1
Kind2
...

I'm thinking of making another table called

RELATIONS

which has these fields..

id | Description (of the Realtionship created)

and another one called RELATIONS_ITEMS

id | Relation | ItemID | Kind | ...

Examples of a REALTION_ITEMS rows would be

1 | 35 | 4 | "Kind1"
2 | 35 | 6 | "Kind2"

So "Kind1" item #4 is related to "Kind2" item #6 through the Relationship #35...

But I don't think the field Kind with a String is an efficient way to do it..

I've got no clue to other way to do it..

If you can make sure that ItemID is unique across all the different items then "kind" is implicit - you won't need that attribute in the RELATIONS_ITEMS table at all.