9

I am designing the database for a portal.Specifically, I have a users table that contains columns: id, username and password.

Also there are three types of users: buyers, sellers and brokers and each user has a separate table with columns: name, description, mobile,introducer etc.The broker table doesn't have introducer column.

Based on this design I want to create a two step registration form with first step login info and the second step profile info.

Now, the business rules dictate that a user can be a buyer, seller or broker.A user can have at most one profile(buyer, seller or broker). I want to keep the login details and the profile info separate in the database

What I have done:

I have created a separate table for users, brokers, buyers and sellers with user id as foreign key in the buyers , brokers and sellers table.

Now my question is

  • How to create the tables for this design?
  • How to specify the foreign key constraints?

I am new to database designing and all help is appreciated.Thanks in advance.

T I
  • 9,785
  • 4
  • 29
  • 51
user3309732
  • 105
  • 1
  • 1
  • 7
  • 1
    What have you tried? What data base engine are you using? What do you mean by "how to create the tables/specify the foreign key constraints"? Do you mean conceptual or syntax issues? – Neville Kuyt Feb 14 '14 at 11:04
  • Neville K, I am using Mysql engine.I am having conceptual issues as to what kind of relationships(eg: 1:1 etc) exist between the user and the buyer, seller and broker tables.I can handle the syntax issues. – user3309732 Feb 14 '14 at 12:22

1 Answers1

4

As "A user can have at most one profile" it sounds like you'll benefit by adding a user_profile table which would then be 'subtyped' by broker, seller and buyer, adding additional fields as applicable.

A user would have a 1:1 with user_profile and user_profile would have 1:0/1 with broker, seller and buyer. I'd consider using the user_id as the primary key to all these.

I think you will also find this answer useful.

enter image description here

Community
  • 1
  • 1
T I
  • 9,785
  • 4
  • 29
  • 51
  • Could you please elaborate your answer – user3309732 Feb 14 '14 at 12:54
  • @T I Thank You for your answer.I think that your answer is close to what I want but one last question.How to implement the one to one relationship between profile table and the broker, seller and buyer table in mysql.Thanks again – user3309732 Feb 14 '14 at 13:48