Results 1 to 2 of 2
  1. #1
    Join Date
    May 2010

    Use of Combination Tables in Design

    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.

  2. #2
    Join Date
    Feb 2004
    One Flump in One Place
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts