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

supertype/subtype

do a search on that for more information

Are you a charity?
Just delete the poor customers. Makes things simpler, and increases your margins at the same time.

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!!!

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.

nice one, mark

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

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.

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)

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.