Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2010
    Posts
    4

    Unanswered: Rearrange data in query- not sure I can do this??

    hi all
    My database is based around some generic "entities". I can set generic properties on these entities, using a second properties table. The pertinent parts of the tables look like this:

    Entities:
    ID (int primary key)
    Name

    Properties:
    ID (int primary key)
    Name
    Value
    Entity


    I'm trying to create a view I can pass directly to a UI. I want to query for a list of particular property names, and have those appear as COLUMNS in the result, with the values underneath.

    E.g if our entities are cars, the properties might be colour, engine size, number of doors.
    I need the properties table to remain as it is - it has to be a completely generic system. I want my query to give me result that looks like:

    Code:
    EntityName | Colour | EngineSize | NumberOfDoors
    -----------------------------------------------------------------------
    Car1           | Blue    | 3.2L          |   2 
    Car2           | Silver   |         
    Car3           | Green  |  1.8L        |   5
    the best I can do at the moment looks like:


    Code:
    EntityName | Property| Value
    -----------------------------------------------------------------------
    Car1           | Colour | Blue
    Car1           | EngineSize | 3.2L
    Car1           | NumberOfDoors | 2
    Car2           | Colour | Silver   
    ...etc
    As you can see some entities may not have certain (or any) properties set. I'd like to show all entities regardless.
    To top it all off, I am using sqlite so am missing some more advanced sql functionality.
    I would really appreciate your help, even if it's just telling me I can't do this with sql. I can after all set up this data using multiple queries and my application code, but it will keep things cleaner if I can do it in one sql statement.

    Many thanks in advance

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by kreyszig View Post
    even if it's just telling me I can't do this with sql.
    You can't do this with sql Not without dynamic SQL anyway.

    This is absolutely best handled by the application code - that is actually the cleaner way. Have you database layer handle data retrieval and storage, have your presentation layer handle presentation of the data.

    Also, in case you didn't know it your design is known as EAV. You will find a lot of tips of working with this type of design if you google around (although most literature will cover the problems of such a design).

  3. #3
    Join Date
    Mar 2010
    Posts
    4
    Thanks very much for the info.
    It looks like I've chosen the right way to model the data I'm working with, and I now wont' be wasting a day trying to get this to work in SQL.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by kreyszig View Post
    It looks like I've chosen the right way to model the data I'm working with.
    Perhaps, but please don't take what I posted to mean this. EAV should absolutely be the very last resort after you have investigated and rejected every other option several times. Perhaps Rudy will chime in shortly with his famous compendium of EAV links.

  5. #5
    Join Date
    Mar 2010
    Posts
    4
    Quote Originally Posted by pootle flump View Post
    Perhaps, but please don't take what I posted to mean this. EAV should absolutely be the very last resort after you have investigated and rejected every other option several times. Perhaps Rudy will chime in shortly with his famous compendium of EAV links.
    thanks..i have to be able to handle completely generic entities as well as a completely generic list of properties. there are also relationships between entities which are (you guessed it) generic, I have a third table to look after these (left and right entities with a relationship definition between them). We may start to hang more specific property tables off the entities later on, but for now it's all generic generic generic.
    Looks like EAV fits perfectly from here, your suggestions welcome though! :-)

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    A common alternative is use of XML datatypes though I admit I don't know enough about your scenario to be able to help much.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i really only have the two links for EAV (the others were for OTLT), although i'm sure there are more out there...

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

  8. #8
    Join Date
    Mar 2010
    Posts
    4
    interesting...perhaps i'll take a look at refactoring.

Tags for this Thread

Posting Permissions

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