Is the database design alright? And I’m quite lost with the admin entity here

database-designerd

A quick description of my system would be: there are 2 users: users and admin.

User registers on the website. Upon registration, the system will generate a QR code for the user. The user then goes to be vaccinated, and upon vaccination, someone will be able to scan his/ her QR code and the status of the user will be updated as vaccinated with the 1st dose of the vaccine. Additional information will also include, the date for the 2nd dose of vaccination, the doctor who was responsible for the vaccination, date and time of the vaccination. The user can also give his feedback for the side effects per dosage.

On the admin's side:

able to log in
they will have a report in the graph for each of the vaccines and the percentage of how many
were vaccinated with these types of vaccines.
graph of side effects per dosage of each of the vaccines
add, edit, and delete a doctor
add, edit, delete a vaccine
it will also include Analytics for the frequency of the users
list of those users

This is the database design I have come up so far and I'm needing help if this is correct and I'm a little lost with the admin entity here.

Best Answer

Authentication and authorization for the administration logic should be handled by the application, so it is natural that the Admin entity will feel disconnected to your domain. You could expand into other entities, such as Permission and Group, so you can have fine-grained control over what exact permissions an administrator, or group of administrators, can have. Enforcing those, however, will be on the application side.

The rest of your design looks pretty coherent. Keep it up!

Related Solutions

Mysql – Personal and Global messaging database design

You could combine recipient and message_status. That would halve the number of inserts.

Database design for actions on some entity

You want to track changes to the items at the item level so your item table would have a key that represents the 'item_id' and something else that distinguishes each state change for that item - you could use a timestamp or a counter or a flag. Let's generically call it 'version attribute'.

Table Design

[LIST]

    LIST_ID
    LIST_NAME
    -- other attributes

[ITEM]

    ITEM_ID
    ITEM_NAME
    LIST_ID -- FK to LIST.LIST_ID
    ACTION_CODE
    -- version attribute (read below)
    -- other attributes

Your LIST to ITEMS relationship would be a one-to-many relationship.

Sample Data [version attribute omitted]

LIST
1    List-1

ITEM        
11   Item-1    1     A -- say added  
12   Item-2    1     A
13   Item-3    1     A
11   Item-1    1     D -- say deleted
13   Item-3    1     C -- say changed

Now there are 2 ways you could track the latest state.

1) Maintain the order of timestamps or counters (if you choose this method) and pick the latest for each item of the list. [Not my preferred way]

2) Add a flag column to your items list say CURR_VER_FLAG and for a given item always pick the record that has CURR_VER_FLAG = 'Y'.

This flag is set to 'Y' (or true if boolean) on the addition of a new item.
When there are any changes to that item you do
```
update ITEM set CURR_VER_FLAG = 'N' where ITEM_ID = ? and LIST_ID = ? and CURR_VER_FLAG = 'Y'
```
and then insert the new record with CURR_VER_FLAG = 'Y'

Your ITEM table would now be

ITEM          
    11   Item-1    1     A   **N** 
    12   Item-2    1     A   **Y**
    13   Item-3    1     A   **N**
    11   Item-1    1     D   **Y**
    13   Item-3    1     C   **Y**-- say changed

I haven't shown it but it should be clear that item_id can no longer be a primary key of this table. You would have to add another distinguishing column probably a surrogate key.

Now you can get

all actions performed on a list
state changes for any item of a list
final state of a list

Note I haven't considered performance here but with right indexes that shouldn't be an issue.

Best Answer

Related Solutions

Mysql – Personal and Global messaging database design

Database design for actions on some entity

Related Question