So in one of my school projects, we have to create a database for a pseudo e-commerce website. The project instructions asks us to implement our database with Boyce–Codd normal form, but I think there're some ambiguity about this normal form.
Let's say that we implement the entity Users like that :
Users(*email, username, password, some_other_fields)
(note: * meens primary key)
First of all, if I understood well the BCNF, this entity isn't BCNF. If the usernames are unique as well as emails, then we can also define this entity like this :
Users(*username, email, password, some_other_fields)
My first question is how to create this entity in Boyce–Codd normal form ?
Then I have another issue with this BCNF form : the missing id. Assuming an user can change his username and his email. The primary key will also be change. My issue is that I don't really have a temporal constant that define an element in my entity. This implies, for example, some issues about logging : assuming we log action from an user with the primary key, if [email protected] change his email to [email protected], we can have this kind of logs :
[[email protected]] : action xxx
[[email protected]] : action yyy
[[email protected]] : action zzz
Then if we don't catch the email change, all our precedent logs means nothing : we don't know who is [email protected].
Then, my second question is don't you think that using a temporal constant id (an integer for example) is more secure ?
Uniqueness is not enough for BCNF. BCNF stresses on Functional Dependency. That is, whether attributes are dependent on the key functionally.
In this case attributes cannot depend on the email. Emails can be changed, inactive, reclaimed by someone else. Therefore, being unique does not justify it enough to be a candidate key. Username may have a higher dependability if functionality restricts the user name to get changed.
Functional Dependency inherently depends on Functional Design. If the application you are creating the table for assumes that usernames will never be allowed to change, then the attributes can depend on username to be a candidate key. If the functional design does allow the username to be changed, then you need to introduce or combine a key that is both unique and functionally dependable.
In case of introduced additional unique ids, they are not 'inherently ' more 'secure' than username here. But they 'feel' or 'become' secure, because presumably the functionality and functional design do not expect the id to be changed. Again, if your functional design allows that id to be changed, then that will not remain secure. Eventually it all depends on your functionality, requirements, and how your attributes are expected to behave according to that functional spec.
If you must have to consider introducing an ID, being not satisfied with dependability of username, then instead of an int/integer, consider rather a GUID, for many many reasons such as the following: