Mysql – User Table Design

database-designMySQL

I am designing for an ecommerce application. I need to know how to implement the following task.

There are 2 options,

Unregistered Users (Free Listing).
Registered Users.

The unregistered users will give all their data but they are not registered with our site.

The registered users will give data and become a partner with our site.

I have designed the table to store the data for both user types on a single user registration table. Now I need to separate both these users, so that I can handle them separately. How to do this?

I am expecting some kind of reference / books on enterprise level database design strategies, so that I can learn and understand.

Thanks in advance.

Best Answer

Make a generic table call Users with a unique ID by line (user_id)and put your generic infos (email, password...), used for both Registered and Unregistered users.

Next, create a table "Registered_Users" to store specific info, with a column user_id (which point to Users table : foreign key).

If you want to query just global info about all your users, query :

SELECT ...
FROM Users u

If you want to query your specific info about registered users, query:

SELECT ...
FROM Users u
JOIN Registered_Users re
ON (u.user_id = re.user_id);

Max.

Related Solutions

Sql-server – Looking for example design of using an SQL database as a queue for throttling web service requests of multiple users

I did exactly this as a project back in 2008-2009, a web service (billing) that needed to handle 1M+ calls per day. I used SQL Server table as a queue, and the lesson from that project I distilled into the article Using Tables as Queues. Stick to the rules I lay out there, and specially don't try to add any whistles and bells to your table, use it exclusively as a queue. Under load I found that a critical issue was to batch dequeue (dequeue 100 web service call request in one DB operation) and handle the dispatch in the app (place the dequeue request into an in memory list, have the web call handlers pick up work from this list). Doing the web service call async is critical. Also, you must read about the ServicePointManager.DefaultConnectionLimit.

Should I create a separate user table for different web products within the same platform

A user is a user, which is different than a person.

A user is a role played by a person. A recruiter is also a role played by a person.

create table party(
  id serial primary key,
  name text not null
);


create table role_type(
  id char(1) primary key,
  name text not null unique
);


insert into role_type values 
('u', 'user'),
('r', 'recruiter');

/* use Table Inheritance to add role-specific details. */
create table party_role(
  party_id integer not null references party(id),
  role_type char(1) not null references role_type(id),
  ...
  primary key (party_id, role_type)
);

/*  create a user: */

insert into party values
(1, 'Neil');
insert into party_role values
(1, 'u');

/* create someone that is both a recruiter and user: */
insert into party values
(2, 'Tristan');
insert into party_role values 
(2, 'u'),
(2, 'r');

Best Answer

Related Solutions

Sql-server – Looking for example design of using an SQL database as a queue for throttling web service requests of multiple users

Should I create a separate user table for different web products within the same platform

Related Question