Hi,
using the username as primary key is understandable
But as this might change and has some meaning to it, but I strongly suggest and wish for, that
you also give us an independent primary key (ID) that can never be changed (by the user). Something like an autoincrement unique integer value (in the users and usergroups table).
Currently it's a problem writing the username to some other table - as that name might change. If we could simply write the ID this would never be a problem and data integrity would always be there (as long as we set some constraints in the db ...) - but I am sure you get what I mean.
Olaf
Give us username-independent IDs
Give us username-independent IDs
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
Re: Give us username-independent IDs
Hi,
I want to extend/clarify/suggest:
membership_users
add field id (int, autoincrement, primary key)
set memberID to unique
membership_userrecords
add field id (int, autoincrement, primary key)
(add foreign key constraint to table membership_users.id. Here a decision would be needed what happens, when a user is deleted: set NULL, IGNORE? It's a little problematic, as when a user is deleted who becomes owner anyways?)
membership_usersessions
add field id (int, autoincrement, primary key)
add foreign key constraint to table membership_users.id (on delete: cascade, on update: cascade)
membership_userpermissions
add field id (int, autoincrement, primary key)
add foreign key constraint to table membership_users.id (on delete: cascade, on update: cascade)
membership_groups
set field name to unique. More important: rename field as name is a keyword in MySQL ( https://dev.mysql.com/doc/refman/8.0/en ... detailed-N )
membership_grouppermissions
set foreign key contraint to membership_groups.groupID (on delete: cascade, on update: cascade)
Of course function getMemberInfo should be extended to also return the id of the user.
I might have missed something - was just a quick peek.
Olaf
I want to extend/clarify/suggest:
membership_users
add field id (int, autoincrement, primary key)
set memberID to unique
membership_userrecords
add field id (int, autoincrement, primary key)
(add foreign key constraint to table membership_users.id. Here a decision would be needed what happens, when a user is deleted: set NULL, IGNORE? It's a little problematic, as when a user is deleted who becomes owner anyways?)
membership_usersessions
add field id (int, autoincrement, primary key)
add foreign key constraint to table membership_users.id (on delete: cascade, on update: cascade)
membership_userpermissions
add field id (int, autoincrement, primary key)
add foreign key constraint to table membership_users.id (on delete: cascade, on update: cascade)
membership_groups
set field name to unique. More important: rename field as name is a keyword in MySQL ( https://dev.mysql.com/doc/refman/8.0/en ... detailed-N )
membership_grouppermissions
set foreign key contraint to membership_groups.groupID (on delete: cascade, on update: cascade)
Of course function getMemberInfo should be extended to also return the id of the user.
I might have missed something - was just a quick peek.
Olaf
Some postings I was involved, you might find useful:
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view
SingleEdit - Prevent concurrent edits on records; Field Permissions; Column-Value-Based-Permissions; Custom (error) message; Audit Log; Backup your database; Two Factor Authentication; Block brute force (failed) logins; Add 2nd SAVE CHANGES button; Place a search on details view