Very much embryonic and alpha project to play with commands which would be required to implement a electronic door lock system.

Key cards with a serial number are issued and each door has a Wiegand or rfid style reader, optionally requiring a PIN as well as card to unlock. Maybe bluetooth instead of PIN?

Also, by abstracting the keypad / reader and lock from the physical device, it should enable MQTT enabled devices to be included. If they are to be trusted….. So PKI is probably needed to maintain trust and security. (Trust but Verify!)

person present card to reader

card id read and submitted to control system with reader (door) id

card id used to get user and door information from db

-> > select userID, cardID from keycards where cardNumber = 1234;
+--------+--------+
| userID | cardID |
+--------+--------+
|      3 |      1 |
+--------+--------+

note cardID is an index to the table, use this as it allows a card ID to be changed if a card is lost

select door information from readerID to decide if PIN is needed.

 select * from doors where readerID = 7678;
+--------+----------+-----------+---------+
| doorId | readerId | doorDesc  | pinReqd |
+--------+----------+-----------+---------+
|      3 |     7678 | Back door |       1 |
+--------+----------+-----------+---------+

Get access level for user

 SELECT accesslevel FROM access WHERE userID = 3;
+------------------------------------------------------------+
| accesslevel                                                |
+------------------------------------------------------------+
| {
        1 : "denied",
        2 : "denied",
        3 : "denied"
} |
+------------------------------------------------------------+

so card number 1234 is cardID 1 and userID 3. presented to reader 7678 which is doorID 3 back door, as it is external it requires a PIN as well userID 3 has access level to doorID “3” of “denied”.

show tables;
+-------------------------+
| Tables_in_accesscontrol |
+-------------------------+
| access                  |
| doors                   |
| keycards                |
| users                   |
+-------------------------+
23 rows in set (0.000 sec)

rb-projects-access:sqlexamples