Member Avatar for manoncloud9

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 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 Contributors
  • 1 Reply
  • 106 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by pclfw
Member Avatar for pclfw

This sounds like a very ambitious project for someone who doesn't understand the complexities of the question they've asked.

You might wish to google 'Data Normalisation', followed by 'data optimisation'.

When you understand those two fields you might wish to reframe your question. Or you might wish to buy in a contractor who understands all of these things and will do it for you. They usually turn out cheaper than a screwed up DB in the end.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.