Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    2

    Unanswered: Meta Database Design Select Question.

    Hi,

    I have a Datbase with teh following two tables.

    Table 1: default_en_listingsdb
    it contains the following fields
    ID user_ID Title

    Table2:
    default_en_listingsdbelements
    ID field_name field_value listing_id

    listing_ID in table 2 is equal to the ID in table 1.

    I am building a search for these two tables where i want to return the ID, user_ID and Title. The table data looks like this.

    Table 1:
    ID user_ID Title
    1 1 Test
    2 2 Test2

    Table2:
    ID field_name field_value listing_id
    1 beds 2 2
    2 beds 1 1
    3 city kingsley 1
    4 city kingsley 2

    I want to return only the ID's where they contain both beds = 2 and city = kingsley.. I want to do this with on e select statement if possible.

    Thanks,

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Meta Database Design Select Question.

    I hate this, what Tom Kyte calls the "funky data model". Flexible as you like, but so hard to query (and this is a very simple query!)

    Also, data integrity is non-existent, because there can be no database constraint that stops you putting 2 for city or kingsley for beds...

    Anyway... </RANT>

    select t1.*
    from table1 t1, table2 beds, table2 city
    where t1.id = beds.listing_id
    and beds.field_name = 'beds'
    and beds.field_value = '2'
    and city.field_name = 'city'
    and city.field_value = 'kingsley';

    Yuk!

  3. #3
    Join Date
    Jan 2004
    Posts
    2

    Re: Meta Database Design Select Question.

    Thanks, I knew it was going to be ugly, i agree that this data model in some ways really sucks... ohh well off to make this work.. Thanks again

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Meta Database Design Select Question.

    Originally posted by greengaint
    Thanks, I knew it was going to be ugly, i agree that this data model in some ways really sucks... ohh well off to make this work.. Thanks again
    Everyone "invents" this data model at some point early in their database careers; well, I'm sure I did once anyway. "Hey, look! With this model we don't have to alter tables or application code ever again! The users can just define their own 'fields' whenever they like!" Then later you find that:
    (a) performance sucks very badly, and
    (b) you need to write SQL 17 pages long to produce the simplest report, and
    (c) the data is full of nonsense like beds='4.2' and city = ' kignsley' and startdate = '31 Feb 2004'... and
    (d) nobody likes this system any more

Posting Permissions

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