Database Design for a retail store

Question

I am designing a retail/b2b system but I am having trouble designing the database for it. I have 2 kinds of customers, Individuals or Businesses/Companies. Individuals have attributes like first name and last name.... whereas companies have a company name and registration code for example instead. I am also planning to have a Supplier and Manufacturer/Brand entity, are they also considered a company?

In other words, in terms of EER, should I have something like Customer Super Class that has Individuals and Customer_Company as Sub classes, and Company Super Class with Customer_Company, Supplier and Manufacturer as Sub classes, keeping in mind a Supplier can also be a Customer... And how would I map these generalizations/specializations to the database.

Also keeping in mind that I have to connect them to the stock/products and invoice parts of the database which I am also still trying to design...

Ehh, I don't know maybe I am over-complicating it but any advice on how to set up the tables would be appreciated.

Side Note: I might be implementing the database in Postgresql if it matters

Edit:

So I am trying to build an app around a store system. The store is a computer part store that sells to businesses as well as individuals. I want an inventory/stock system with supplier and manufacturer/brand for items, purchasing system (invoice/order, payment cash/check), customer system with individuals each having a different price for items depending on loyalty, and businesses with different dealer prices (dealer, super dealer) and quantity. The app might be extended to feature an online shop.

Keep in mind that right now I am only focusing on the customer part of the database trying to make it right and didnt give much thought to the rest of the systems yet, so as I continue building the other parts of the database more questions may come in mind and may affect how the customer part of the database should be.

Questions that are coming to my mind:

How would I set up the customer tables to account for the different kinds of customers and their attributes? A company that might be a customer (or what I mean by customer is buyer) could also be a Supplier. An idea that came to mind was to make an “Account” and have a flag that says this is a company or a person account... but that would mean that there will be null values for the attributes that don’t belong to one of the groups. What’s the best practice here.

Answer 1

PostgreSQL supports an advanced object-relational mechanism known as inheritance . Inheritance allows a table to inherit some of its column attributes from one or more other tables, creating a parent-child relationship. A child table is created with the CREATE TABLE SQL command by using the INHERITS clause. This clause consists of the INHERITS keyword, and the name of the table (or tables) from which to inherit.

Here is the portion of the CREATE TABLE syntax which applies to inheritance:

  CREATE TABLE  childtable
    ( definition )

         INHERITS ( 
parenttable
 [, ...] )

You can create a parent table Company :

CREATE TABLE Company (
// the common attributes)

CREATE TABLE Business (
// the specific attributes
) INHERITS (Company)

CREATE TABLE Individual (
// the specific attributes
) INHERITS (Company)

keeping in mind a Supplier can also be a Customer

I think in this case it's better to seperate the Supplier and the Customer. In other words you can find the same Supplier in the Supplier table as a supplier and the Customer in the table Customer as a customer.

There are three types of relationships:

• One-to-one: Both tables can have only one record on either side of the relationship. Each primary key value relates to only one (or no) record in the related table. They're like spouses—you may or may not be married, but if you are, both you and your spouse have only one spouse. Most one-to-one relationships are forced by business rules and don't flow naturally from the data. In the absence of such a rule, you can usually combine both tables into one table without breaking any normalization rules.

• One-to-many: The primary key table contains only one record that relates to none, one, or many records in the related table. This relationship is similar to the one between you and a parent. You have only one mother, but your mother may have several children.

• Many-to-many: Each record in both tables can relate to any number of records (or no records) in the other table. For instance, if you have several siblings, so do your siblings (have many siblings). Many-to-many relationships require a third table, known as an associate or linking table, because relational systems can't directly accommodate the relationship.

Your database system relies on matching values found in both tables to form relationships. Those matching values are the primary and foreign key values. (The relational model doesn't require that a relationship be based on a primary key. You can use any candidate key in the table, but using the primary key is the accepted standard.)

• A primary key, also called a primary keyword, is a key in a relational database that is unique for each record. It is a unique identifier, such as a driver license number, telephone number (including area code), or vehicle identification number (VIN). A relational database must always have one and only one primary key.

• A foreign key is a key used to link two tables together. A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.