Question Locked

Best methods for handling poli-morphism and MySQL

0 helpful
2 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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 5.51.

Thanks

Josh

2 replies

by tony hopkinson

One is good old objectstore. ie you stream your java class instances into a table, along with an absolute minimum of extra info ClientID and rich/poor may be. Works very well for document based systems where there isn't a lot of cross relations.

Forget things like adhoc queries and reporting and such.

Another approach is to go completely the other way.
You have a table of properties. e.g wealth, debt etc. and then you tie them togther with ClientId. Again very flexible but querying out to a fixed format row is time consuming.

Whether you do the presentation through views, sps, or your classes, you get both the benefits and the costs of a very tightly coupled design, or an ever growing shed load of mappings.

Third option is to have anonymous field names e.g. Text1 Nuber15 Money5 etc. and a type identifier then you map the dn columns to your class properties. To me this gives you the worst of the above two approaches, but some are enamoured of it.

Fourth is you don't actually do poly morphism as such You have clients, RichClients, PoorClients, a lot of relations. More of an amalgamation approach than a polymorphic one and generally my preferred approach.

Last but far from least an unholy confabulation of the above four ideas, ie where you would end up without constant refactoring. :( :( :(

HtHs

This discussion is closed to new replies.