The Data Model Resource Book, Vol. 1: A Library of Universal Data Models for All Enterprises

Have you seen the SO question relational-database-design-patterns?

EDIT: Having got interested in this question I had a look for any books on the subject and found the following volumes:

The Data Model Resource Book: A Library of Universal Data Models for All Enterprises: Vol 1

The Data Model Resource Book: A Library of Universal Data Models by Industry Types: Vol 2

The Data Model Resource Book: Universal Patterns for Data Modeling Vol 3

I've not read the series, so I can't recommend them myself, but they look interesting.

Check out:

http://www.amazon.com/Data-Model-Resource-Book-Vol/dp/0471380237/ref=dp_ob_title_bk

and the volumes 2 and 3. I am sure you will find a full schema for your problem there ;)

Each OFBiz component is build around group of entities/business domain, which roughly correspods to a chapter from Data Model Resource Book vol 1. I think reading that book will help you understand how the component is supposed to work

Check out the Data Model Resource Book by Len Silverston:

http://www.amazon.com/Data-Model-Resource-Book-Vol/dp/0471380237/ref=pd_bbs_sr_1?ie=UTF8&s=books&qid=1232336996&sr=8-1

It approaches reusable design from the data model point of view, as opposed to end user requirements or OO designs. However, I find that to be very useful - once you have a good grasp of the data model, you have a big jump on the requirements and the entities that will eventually be modeled as classes.

You may want to look into Len Silverston's Party Model. Here's a link to his book: http://www.amazon.com/Data-Model-Resource-Book-Vol/dp/0471380237.

I have no experience building something on that scale, though I think that thinking of it as 500k rows x 500 - 1000 columns sounds a bit ridiculous.

This is not a great design.

You should read up on the Party Model, and Table Inheritance to see a design built to last. A skeleton:

PARTY
id

ORGANIZATION : PARTY
name
doing_business_as

INDIVIDUAL : PARTY
first_name
last_name

PARTY_RELATIONSHIP
from_party_id references party(id)
to_party_id references party(id)
from_date
to_date

ORGANIZATION_CONTACT_RELATIONSHIP : PARTY_RELATIONSHIP

If you're just looking for data models, I recommend the following books:

• The Data Model Resource Book, Vol. 1: A Library of Universal Data Models for All Enterprises
• The Data Model Resource Book, Vol. 2: A Library of Data Models for Specific Industries
• The Data Model Resource Book: Universal Patterns for Data Modeling

I have Vol 1 and Vol 2 and these have been pretty helpful in the past.

I find the Data Model Resource book to be a good source of inspiration for modeling business structures. Apache Ofbiz ERP was built around the concepts in this book.