Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2007
    Posts
    5

    Single columns with multiple data types. possible ?

    I want to have a table with 2 columns. Key and Value. The value can be of 3 different data types (date, double, string)....

    Now in my front end applications which access the table i want to receive the data in the correct data types. i.e. date as date, string as string and so on....

    One way i thought about is that i create 3 tables. so depending on what table i am selecting the value from i will do the CAST in the select statement. It however doesnt seems to be a very clean solution. Any other idea i think of doesnt seem to be normalized well. Can anyone share their thoughts on this ?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, not possible

    and 3 separate tables is an improvement but still a poor design

    is this what you are trying to do? (hint: don't) --

    Dave's guide to the EAV

    One True Lookup Table by Joe Celko

    OTLT and EAV: the two big design mistakes all beginners make

    Lookup Table Madness

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

  3. #3
    Join Date
    Feb 2007
    Posts
    5
    Thanks a lot for the links. Def good info. I had not thought everything through but i was expecting major issues in querying the database with such design. The only reason for using this is that i thought adding of columns can be done on the fly. Now clearly all the links give the verdict to not use EAV correct ? However none of them compare the two design on the task of adding a new attribute column ? How would it affect my front end code and everything ? Would EAV still be a bad design ?

  4. #4
    Join Date
    Feb 2007
    Posts
    5
    Also unfortunately its my boss who came up with the EAV idea so i think i would have to go with that one. I did send him those links but he doesn't like the COMPACT design as the code will change if any columns are added/removed....

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Maybe your boss would like The Associative Model of Data. Not that I'm recommending it: it looks like EAV on steroids to me, and I wrote one of the anti-EAV articles Rudy listed above!

Posting Permissions

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