My problem:
- On a
PHP/MySQL
application I have to distinguish theregistered accounts
from thesimple visitors
. Both of them can perform actions which are written into the database. Now I use a separate table forvisitors
and a separate table forregistered accounts.
My example solutions so far:
Option #1:
Registered accounts
andvisitors
are separated into two tables. Both of them can havesessions
and the relationship between the tables arenon-mandatory
, since a person is either avisitor
or aregistered account
.Option #2: Everyone is in one table and the relationship between the tables are mandatory, since everyone can have
sessions
, but since a person is either avisitor
or aregistered account
, he might not have anemail
orpassword
, so that can be NULL. This might lead to errors in the integrity.
My question:
- Should I keep the
accounts
in a separate table from thevisitors
or I can put everyone in one table with a BOOLEANisThisAccount
variable that indicates whenever the person is aregistered account
or just a simplevisitor
?
I also am open to different approaches.
I would probably go with a third option: Link sessions to visitors and visitors to accounts. That way you need only one field
visitorID
in thesessions
table and only oneNULL
column (accountID
invisitors
). In my opinion that's conceptually cleaner, since people performing actions are always visitors, who may or may not have an account.