How to handle poli-morphism using MySQL

[joshfolgado]

Hi,

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?

Thanks for your time and effort

Josh

[MAK]

Table1: Customer table with a column category that stores [Rich/Poor]
Table2: Rich details
Table3: Poor details

You could create a view by using JOIN clause with RICH or POOR table.

[joshfolgado]

Hi MAK,

Thanks for your message, I appreciate it.

One question if I may...

Any chance you can send me a design that shows your solution? Preferable either for MySQL or MS SQL 2008.

Or it could be a MS SQL sql file that would allow me to create that structure easily.

Thanks once again for your time, effort and expertise.

Josh

[SDas]

Josh, this may just be a matter of terminology, but from your description, there is a serious flaw in the design. Just because someone is Rich doesn't mean they don't have 'poor' problems like unpaid debt. Conversely, just because someone is Poor doesn't mean they aren't good customers.

You probably do need to track the 'deadbeats' and the good customers but the Rich/Poor distinction is (at best) misleading.