[RESOLVED] designing db with admin feature

vidz

store

|     Store ID     |      Description       |    Location  |   Telephone      |    Manager     |
------------------------------------------------------------------------------------------------------
|     00001        |      ABC Stores       |   tyhth       |   0112 675432   |       ffff            |
|     00002        |      XYZ Sores         |    yuyu      |   0112 342123   |      nnn           |
---------------------------------------------------------------------------------------------------

Store_Employee

| Store ID  |  Employee ID |  Assigned Date/Time   | Assigned by | Is Active? |
---------------------------------------------------------------------------------------------
| 00001     |  11111          |  2012/05/26 10:50AM  | Admin         | TRUE       |
| 00002     |  11112          |  2012/05/27 11:50AM  | Admin         | TRUE       |
| 00002     |  11113          |  2012/05/28 12:50AM  | Admin         | TRUE       |
---------------------------------------------------------------------------------------------

this an example how iam assigning employees to stores.

"SELECT * FROM Store_Employee WHERE Store_ID = '00001' AND Employee_ID = '11111'" will result not null result.
But same employee going to access store id 00002 with same type select query he will obtained null set. because he isnot asigned to that store.

my problem is this, how can i implement admin who can access all stores in these conditions. Your ideas are highly appreciated

Bonesnap

I wouldn't use the store's ID as the primary key for the employee table. Is it? Neither table looks like it has an auto incrementing primary key.

In any case, you're essentially asking how to assign an employee to multiple stores. I would use a joining table.

vidz

Bonesnap;11005339 wrote:
I wouldn't use the store's ID as the primary key for the employee table. Is it? Neither table looks like it has an auto incrementing primary key.

1. store_id is foreign key for store_employee table. there isnt 1ry key for store_employee table. employee_id an store_id are foreign keys. There is a separate employee table as well. My intention of having store_employee table is to assign employees to the stores easily.

I am a beginner can you pls tel me why it is important to have an auto increment 1ry key over normal 1ry key in inventory control systems.
My problem is how to add an admin. there are about 12 stores. so do i have to add 12 records in store_employee table or are there any easy way of doing this

Derokorian

What bonesnap is saying, is turn the store_employee table into just employee and drop the store_id column. Then add another table called store_employee which stores store_id and employee_id to form a relationship between the two. One person can be in multiple stores and obviously each store can have multiple employees. You can then store store specific data in this record (maybe active would belong here?). Otherwise to assign one person to multiple stores you end up having repeated data and risk inconsistency upon update, designing against this is called normalization.

vidz

Thanks both of u. 🙂