Hi everyone,

I've been charged with creating a complex database that includes all kinds of entities with common data elements including customers, users, vendors, shops, organizations, etc.. I've worked on databases with separate tables for each primary entity like (customer table, lead table, vendor table, user table, organization table).

My question is simply this: Because all of these types of entities have very common data elements including (name, address, phone numbers, etc.) can much of the data be rolled up into a single "Entity" table? ... Or will that cause problems if eventually I'll have to deal with relationships like vendor(x) is an affiliate of organization(y), or user(x) is a sponsor of Organization(y), etc.

I've worked on databases that combined Customers, Vendors and Leads into a common Business_Partner table. I would be expanding this idea if I took this route.

Can any one offer any advice on the benifits or pitfalls for taking the traditional individual table for each partner type or if a common table with a field to identify the partner type (i.e., isCustomer, isVendor, isUser, isAffiliate, isSponsor, isSupporter, etcv.) would be the best design route to take?

Any advice would be greatly appreciated and Thanks so much in advance.

have a google for sub-type super-type designs. Or check jeff Smith's blog on SQLTeam for inheritance (same design, he just describes it in an OO manner).

This is essentially what you've described.