Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2007
    Location
    Portland, OR US
    Posts
    3

    Question JOIN, Nested SELECT or something else?

    Let me preface this by saying I am open to design criticism if i am making this harder than it needs to be.

    I have 3 tables:

    serials (id, number)
    columns (id, name)
    data (id, serial_id, column_id, value)

    There are different amounts of rows in data that correspond to each row of serials.

    What i am trying to retrieve is each serials.number with all corresponding data.value fields appended to the end of the result row. This is an example of what i am looking for if all corresponding values were stored in a c-style array:

    row 1: serials.number, data.value[0], data.value[1], data.value[2]
    row 2: serials.number, data.value[0], data.value[1], NULL
    row 3: serials.number, data.value[0], NULL, data.value[2]
    row n: etc ...

    The reasoning behind doing it this way was to allow the amount of columns to be different for varying implementations without having to ALTER TABLE. I also wanted the column name to support "full text" without having to write a parser for table field names. In addition to all of this, I need to gather all the information in 1 query as to facilitate WHERE clauses to narrow the results.

    Any criticism/advice is welcome I'de love to expand my knowledge of SQL to avoid these issues in the future.
    Last edited by Exide; 09-03-07 at 23:25. Reason: Spelling error in one of the table fields

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do a search for EAV (entity-attribute-value)

    this is a design technique with severe shortcomings, and i urge you to consider alternatives

    the main problem is that "to gather all the information in 1 query as to facilitate WHERE clauses to narrow the results" turns out to be ~way~ harder than you think

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

  3. #3
    Join Date
    Sep 2007
    Location
    Portland, OR US
    Posts
    3
    I will simplify the situation (1 table?) and try to only inject complexity when needed When doing development in a vaccuum sometimes it is hard to tell which voice to listen too

    Thanks again.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by Exide
    Let me preface this by saying I am open to design criticism...
    ...then you have come to the right place.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Well, no criticism here, but some questions about your reasons:

    Quote Originally Posted by Exide
    The reasoning behind doing it this way was to allow the amount of columns to be different for varying implementations without having to ALTER TABLE.
    Why, exactly, is ALTER TABLE such a problem?

    Code:
    INSERT INTO Columns (ColumnName) VALUES ("foo");
    
    ALTER TABLE ADD COLUMN foo VARCHAR(255);
    Why is the first syntax so much easier or the second so much harder?

    I also wanted the column name to support "full text" without having to write a parser for table field names.
    Why do you have to parse field names? Are you sure you understand what "parsing" means? If you really do have to parse field names, does your language / standard library not support regular expressions?

    In addition to all of this, I need to gather all the information in 1 query as to facilitate WHERE clauses to narrow the results.
    Why is one query important? Can't you cache the schema information?

    And what, exactly, is stopping you from gathering all this data in one query? Have you looked into stored procedures?

    Finally, have you thought about how you're going to handle integrity constraints?

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Quote Originally Posted by sco08y
    And what, exactly, is stopping you from gathering all this data in one query? Have you looked into stored procedures?
    Or views ?
    George
    Home | Blog

  7. #7
    Join Date
    Sep 2007
    Location
    Portland, OR US
    Posts
    3
    Quote Originally Posted by sco08y
    Why, exactly, is ALTER TABLE such a problem?

    Code:
    INSERT INTO Columns (ColumnName) VALUES ("foo");
    
    ALTER TABLE ADD COLUMN foo VARCHAR(255);
    Why is the first syntax so much easier or the second so much harder?
    It is not really a problem rather a feeling that i was approaching the situation wrong. Which it appears i was by second guessing myself Fields will be added and removed at a handful of user's whims and it seemed dangerous to have those changes directly affecting all the data (being that it is only 1 table). I realize that it is my responsibility to make it not dangerous by doing thorough validation and utilizing integrity constraints.

    Quote Originally Posted by sco08y
    Why do you have to parse field names? Are you sure you understand what "parsing" means?
    With a single table using the ALTER TABLE method, i will be using the field names as headers for the end result. If the field name is more than 1 word i will need to perform some kind of "beautification" on it before displaying it to the screen. For example I don't want the header to show as "last_modified", but instead as "Last Modified".

    Quote Originally Posted by sco08y
    If you really do have to parse field names, does your language / standard library not support regular expressions?
    Yes it does. I will be using such to handle the "beautification" of the header names.

    Quote Originally Posted by sco08y
    Why is one query important? Can't you cache the schema information?

    And what, exactly, is stopping you from gathering all this data in one query? Have you looked into stored procedures?
    One query was only important because of my lack of experience with SQL. The only other alternative i could come up with at the time was pushing the data from all 3 tables into structures and then manipulating it from there. This seemed like a horrible idea and thought i could utilize SQL queries a little better. That is how i ended up here

    Quote Originally Posted by sco08y
    Finally, have you thought about how you're going to handle integrity constraints?
    I have, and am working that out currently.

    Quote Originally Posted by georgev
    Quote Originally Posted by sco08y
    And what, exactly, is stopping you from gathering all this data in one query? Have you looked into stored procedures?
    Or views ?
    Views, yet something else i need to familiarize myself with Quick glance leads me to believe this could have yielded a solution.

    I have bookmarked some decent resources on views (as well as stored procedures and EAV). Thanks for all the responses.
    Last edited by Exide; 09-07-07 at 18:13.

Posting Permissions

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