Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2009
    Posts
    7

    How to handle poli-morphism using MySQL

    Friend, I have a challenge I need your help with...

    We use poli-morphism with Java classes and I need to duplicate this with a database design... let me explain...

    Lets assume:
    - we have customers
    - customers can either be rich or poor
    - we want to store different details for rich and poor customers; i.e.for poor customers we may want to store information about payment problems, unpaid debt, etc. For rich customers we may want to store details about their assets and buying patterns...
    - both rich and poor customers buy products

    What is the best design you can think off? We currently use MySQL.

    Thanks

    Josh

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by joshfolgado
    What is the best design you can think off?
    supertype/subtype

    do a search on that for more information
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Are you a charity?
    Just delete the poor customers. Makes things simpler, and increases your margins at the same time.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    You could migrate to PostgreSQL and use table inheritance.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by loquin
    You could migrate to PostgreSQL and use table inheritance.
    this is an inappropriate comment, especially for a moderator

    at least, that's what i was told when i made a comment like that (which partly explains why i'm no longer a moderator)

    you didn't even include a smiley!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  7. #7
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    Have a customer table with a unique ID for each customer and a flag to say if he is a rich or poor customer.

    Have a rich customer information table that contains the customer ID and the attributes you want to store for rich customers.

    Have a poor customer information table that contains the customer ID and the attributes you want to store for poor customers.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nice one, mark

    so, basically, a supertype/subtype table structure, yes?

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

  9. #9
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    If you say so. I have been doing database design a long time and have seen a lot of buzz words come and go. Thirty years ago we used to call design as being a master with multiple detail tables.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by MarkATrombley
    Thirty years ago we used to call design as being a master with multiple detail tables.
    interesting that you should say that, because "tables" did not hit the mainstream for another decade or so, when relational databases were introduced (i started with DB2 in 1987)

    i'm sure you were using master/detail concepts, maybe with IMS?

    i also used the same concepts, but they were called owner/member in CODASYL (the standard that preceded relational databases)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    Thirty years ago I was working on a DEC PDP-8 with 64K of memory, programming in Dibol (DEC's version of Cobol). We used files with indexes back then, not a relational database. The concepts were the same regardless. I pay a lot more attention to storage concepts than I do the buzz words.

Posting Permissions

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