MySQL structural integrity versus simplicity?

51 views Asked by At

My problem:

  • On a PHP/MySQL application I have to distinguish the registered accounts from the simple visitors. Both of them can perform actions which are written into the database. Now I use a separate table for visitors and a separate table for registered accounts.

My example solutions so far:

  • Option #1: Registered accounts and visitors are separated into two tables. Both of them can have sessions and the relationship between the tables are non-mandatory, since a person is either a visitor or a registered account. enter image description here

    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 a visitor or a registered account, he might not have an email or password, so that can be NULL. This might lead to errors in the integrity.

enter image description here

My question:

  • Should I keep the accounts in a separate table from the visitors or I can put everyone in one table with a BOOLEAN isThisAccount variable that indicates whenever the person is a registered account or just a simple visitor?

I also am open to different approaches.

1

There are 1 answers

0
Tomaso Albinoni On BEST ANSWER

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 the sessions table and only one NULL column (accountID in visitors). In my opinion that's conceptually cleaner, since people performing actions are always visitors, who may or may not have an account.