0

I have to build a web app with the following db structure: I have the structure as in the sketch.I have to separate the people to person and to user because of there are people who will never have user account and just simple added by administrator to the site db.

There is an application form to register to the site. There must be fill the person fields and the user fields

If the admin adds the person to the db(without user info),and this person wants to register later by him/herself how to deal with this case?

He/She tries to register but filling the Person fields will duplicate the data. In this case Person fields already exist. Do you have any idea?

Update1: Thank you for your answers i already known that how to store the data fields in the tables.But don't know how to deal the case when the admin register the person data and that person comes to register him/herself PS:forgive me but i don't have a modeller tool now.

Person

User

Andrewboy
  • 364
  • 5
  • 15
  • Does the Person table contain unique columns that can be used to identify the person, like email for example? http://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists might help you forward. – harris Sep 16 '15 at 15:00
  • i attached the tables sou you can understand better now my issue – Andrewboy Sep 16 '15 at 15:47
  • I assume the email column is the person's personal email, and therefore something that should be unique. You should add a unique constraint to it for the sake of consistency. Then when the person registers, you should check for the existence of the email address. – harris Sep 16 '15 at 15:57
  • Yes the email is unique field. If the user wants to register and no Person exist with his data he will register by filling the Person and User fields(registration forms contain it) too – Andrewboy Sep 16 '15 at 16:00
  • So when you handle the registration, you should check the database for the existence of the email and insert a new person row only if it doesn't exist and so on. Do take possible security implications into consideration, as pointed out by @HugoAlonso in his answer. – harris Sep 16 '15 at 16:33

1 Answers1

0

Is important to notice that meanwhile a Person can be or not a User, a User can be also not a Person. (For example, user roles that will deal with automatic maintenance of the database)

Everything is about how your business model is structured.

Leave User as lightweight as possible by putting all data about persons in Person. (name, dataofbirth, email, etc.)

In User just add a Foreign Key nullable into Person table records and only add data about a user in this table (id, password, lastlogin, etc..).

When the form is filled by the user, first add a record in Person and if data corresponding to User is filled, then grab the id of the Person you just added and use it as the data to fill the Foreign Key in User.

If the Person data is already on db, just notify the user and ask him about using that old data or update it... always taking into account security and privacy issues. (This user may not be who he is claiming to be.. maybe you should ask him to confirm his email as many newsletter do)

Hugo Alonso
  • 6,684
  • 2
  • 34
  • 65
  • i designed the database as you wrote.Confirming by email can be a good idea but only works if the email address is already available – Andrewboy Sep 16 '15 at 15:56
  • You can check for name, or other unique details already present and if they match ask the user to confirm that's him/her and do a merge over that data. You may have to endup setting some fields of Person to not nullable. – Hugo Alonso Sep 16 '15 at 17:05