Results 1 to 12 of 12

Thread: The "God" table

  1. #1
    Join Date
    Jan 2008
    Posts
    186

    Unanswered: The "God" table

    I have a 2 tables: machines & properties. The properties table contains the names of certian properties I wish to read about a machine

    Properties are read from a machine twice a day

    Now, I want to gather the results of those reads into a table. Instead of making a separate table for each property, I want to make a "data" table that links properties with the machine.

    So something like:
    Code:
    CREATE TABLE data(
    read_time TIMESTAMP,
    machine_id int not null,
    property_id INT,
    property_value INT
    )
    The problem: Not all values are INT. Some are string, some can even be floating point numbers!!!

    Is there an easy way to accomplish what I'm trying to do other than having to create a separate table for each property?

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use VARCHAR for property_value in the "data" table

    where's this "God" table you were talking about?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by r937
    use VARCHAR for property_value in the "data" table

    where's this "God" table you were talking about?
    I was thinking about that, but I realized we will also need to run reports on these tables. And wouldn't the use of VARCHAR severly slow down the query speed for numeric values (compared to if it were an INT column)?

    Hmm, though, I suppose it's a sacrifice I'll have to make if I want to use this structure.

    (Oh, P.s. The god table was just a nickname for my "data" table, b/c it combined all properties into one table)

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Danger. I smell EAV lurking.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jan 2008
    Posts
    186
    Hmm... I'm not familiar with that term. Could you please elaborate?

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Jan 2008
    Posts
    186
    Wow...what a debate.

    I'm convinced.

    Okay, no EAV table.

    Thanks

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You do win, though, the Best Thread Title of the Year (so far) though IMHO
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    perhaps, but "the devil table" would be more appropriate.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Thrasymachus
    read this...
    Sean - GREAT reminder of a terrific thread. Your contributions were, in retrospect, the best (not being sarcy - you didn't get drawn in and made, in my count, two "cut to the chase" posts).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by jezemine
    perhaps, but "the devil table" would be more appropriate.
    Does he get to vote? I'm not sure but I think the rules mention something about adorableness and persecuted minorities...
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by pootle flump
    Sean - GREAT reminder of a terrific thread. Your contributions were, in retrospect, the best (not being sarcy - you didn't get drawn in and made, in my count, two "cut to the chase" posts).
    aaahhhh shucks, your stuff is pretty groovy too.

    I may not always have the time or patience to go to the lengths you guys do, but I like to think I get my shots in. There I used less of my technical knowledge and more of my experience in playing the dozens and my formal training in logic and rhetoric.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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