Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009
    Posts
    2

    Link a Field Value to a table... DB Design help

    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
    Last edited by xchekox; 05-12-09 at 15:58.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by xchekox
    Is this and efficiente way to do it?
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2009
    Posts
    2
    ^^ 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..
    Last edited by xchekox; 05-13-09 at 11:12.

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •