how we use OOP to design complicate table schema/relationship
Don't! Relational databases have been around a long while and are very good at managing relational data. Normalization is a well-understood method of getting an efficient, good ER design. Trying to apply a design methodology to a technology for which it is not intended would eb a poor compromise at best. If you apply OO in the ER domain, you tend to get very inefficinet table design. If you are sure you want an OO data layer, consider an OO database. Otherwise stick to the tried and tested ER route and implement an ORM layer. [ June 23, 2004: Message edited by: Paul Sturrock ]
"Natural"? Yes - since your domain objects will be modelled via OO. Best practice? No, since you tend to end up with very poorly designed relational data model. Have a google for "Object Relational Impedance mismatch" to see how much arguement there is around this topic. A summary of it is that basically relational databases have been built and refined to handle data in a relational way, but modern software tends to handle data in an OO way, so you end up with a translation problem between the two. Think about the normal best practice for modelling N-N relationships - you would use a lookup table. But you'd never model an object that is just two FKs would you - since it has no natural meaning in an OO design.
An "OO database" (as I rather lazily called) it is strictly an Object-Relational Data Base Management System, or ORDBMS. One of these would be useful if you insist on OO design for your data layer. If you are really interested "The Object Database Standard: ODMG-V2.0" tries to define a standard for OO databases (you'll find it at the Object Data Management Group. Have a google for ORDBMSs - you'll find a bunch of products. I don't know if anyone has different experience, but I've yet to see one used on a large enterprise application.
Joined: Jan 23, 2002
Strictly speaking, an "OO database" can also be a native object-oriented database instead of an ORDBMS.