uint
uint

Reputation: 19

DB structure for users of different groups and fields

There are two types of customers, it's individual and corporate customers. For example they have same fields (email, password), but corporate customers has unique fields (company name, company phone, address). What else could be the structure of the database?

mysql> desc `customers`;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id_customer | int(11)     | NO   | PRI | NULL    | auto_increment |
| email       | varchar(32) | NO   |     | NULL    |                |
| password    | int(16)     | NO   |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> desc `corporate_customers`;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| id_customer     | int(11)     | NO   | PRI | NULL    |       |
| company_name    | varchar(32) | NO   |     | NULL    |       |
| company_address | text        | NO   |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Upvotes: 0

Views: 75

Answers (3)

Blaise Swanwick
Blaise Swanwick

Reputation: 1755

No, it's not the only way to build your tables. If you are looking for other suggestions. You might consider a "person" table, a "company" table, and a "customer" table and two many to many tables called "person_customer" and "company_customer".

"PERSON"
  person_ID, PK
  email
  password
"Company"
  company_id, PK
  company_name
  company_address
"Customer"
  id_customer, PK
"person_customer"
  person_ID, FK
  id_customer, FK
"company_customer"
  company_id, FK
  id_customer, FK

That example gives you a distinct Customer object, and distinct person and company objects. The problem that I see with your example is that you have "id_customer" as a primary key in two different tables. I would consider that bad form. How you build the tables is up to you, but I see a problem with that.

Upvotes: 1

Yogendra Singh
Yogendra Singh

Reputation: 34367

This works. Only thing in addition you may want to do in corporate_customers table is to add a unique constraint as below:

  CONSTRAINT uc_corporate_customers UNIQUE (company_name,company_address)

This will make sure there are no duplicate compnay_name and company_address for a customer.

Upvotes: 0

Csak Zoli
Csak Zoli

Reputation: 428

You could be more specific on what you are trying to accomplish. There is no problem whit the database structure of course but your not specifying anything.

If you are trying to do something like checking which id's match from the 2 tables and selecting company_name , company_address here's how but how I was saying you could be more specific about what you want.

SELECT corporate_customers.company_name ,corporate_customers.company_address FROM corporate_customers WHERE corporate_customers.id_customer = customers.id_customer

Upvotes: 0

Related Questions