hretic hretic - 1 month ago 13
MySQL Question

Best method for separation of ADMIN and USERS entries in a database table

lets say i have separate table for users and admins

users : username , password , email , name
admins : username , password , level


now i have a table that both users and admins can insert data in ... like a website for selling books :

books : title , price


so 3 methods comes to mind

method 1 - adding 2 filds to books table for admin and user

books : title , price , user_id , admin_id
----> some title , 1000 , 5 , -1 // user posted book
----> some other title , 2000 , -1 , 12 // admin posted book


when user insert a book i'll save his
id
in
user_id
and put
-1
for
admin_id
and vise versa for admin

method 2 - saving
user_id = -1
for admin entries

books : title , price , user_id
----> some title , 1000 , 5 // user posted book
----> some other title , 2000 , -1 // admin posted book


method 3 - having a user in the users table to represent admins and save all admin entries with this user id -- which doesn't feel like smart thing to do !

users : id , username , password
----> 1 , admin , adminpassword
----> 2 ,user1 , userpassword
----> 3 ,user2 , userpassword

books : title , price , user_id
----> some title , 1000 , 1 // admin posted book
----> some other title , 2000 , 2 // user posted book
----> some other title , 2000 , 3 // user posted book


is there any other way to do this ? which one is more acceptable approach ?

i know we can have both users and admins in the same table and have separate
user_rules
or
user_meta
table for admins users but i dont want to use that design .... im working on a project that already been online for a year or 2 with lots of data in the database ... the original programmer is not available and im adding some new features to the website ... im trying to avoid changing the current structure as much as possible

Answer

I have to agree with Ross. There are only two things you can do with a poorly designed database -- make it a little worse every time you need to make changes or bite the bullet and improve the design.

Ok, the database is a year or two old and has lots of data. That, if you do it right, is not a problem.

create table OwnerTypes(
  ID    char( 1 )     not null primary key -- contains 'U' or 'A'
  Name  varchar( 16 ) not null -- contains 'User' or 'Admin'
  Other [type] -- if needed
);

create table Owners_(
  ID       int       not null  -- could be auto generated
  TypeID   char( 1 ) not null references OwnerTypes( ID ),
  Username varchar( 32 ) not null,
  Password varchar( 32 ) not null,
  constraint PK_Owners primary key( ID, TypeID )
);

create table Users_(
  ID       int       not null primary key,
  UserType char( 1 ) not null check UserType = 'U',
  Email    varchar( 128 ) not null,
  Name     varchar( 32 ) not null,
  constraint FK_Users_Owners foreign key( ID, UserType )
    references Owners_( ID, TypeID )
);

create table Admins_(
  ID       int       not null primary key,
  UserType char( 1 ) not null check UserType = 'A',
  Level    smallint  not null,
  constraint FK_Admins_Owners foreign key( ID, UserType )
    references Owners_( ID, TypeID )
);

Note: MySQL accepts but does not enforce check constraints. You'll have to do that with trigger or other code.

You'll notice I ended three of the table names with an underscore. That is my way of naming a table that is not expected to be directly accessed by application code. As you still have the problem with all kinds of existing scripts and app code that expects to see a Users and an Admins table, we are going to provide that access through views.

create view Users as
  select  o.Username, o.Password, u.Email, u.name
  from    Owners o
  join    Users u
      on  u.ID = o.ID
      and u.userType = o.TypeID;

Now you have a database object named Users that provides the data in the form expected by the apps. Do the same thing with Admins. Finally, write triggers on the views that will intercept DML and perform the operations as needed to maintain the data in the actual form. The app developers don't even have to know anything changed.

This way, you can update your database as needed but allow existing code to continue to operate as before.

Comments