0

I was in a meeting with my boss and technical support leader and they asked me for a database model to a very simple application. That application requires: employees information, some location data, and etc, etc, etc.

One of their database table was:

  • employee

      id
      name
      lastname
      username
      password
    

That table suppose to save all employers' info but since I was available to make any changes as I wanted I did as follow:

   Person
      id
      lastname
      ocupation// saved as int. Ex: 1 =employee, 2 =contacts, 3 = bank name, etc, etc



   User
      id
      person_id
      username
      password

My approach was very simple and extensible since now my table person was able to handle all person information despite whether if it is employee or a dentist or what ever. But my boss and a work-mate said that my approach was a bad idea and not correct and too complicated it(which is not), but what if sometimes later the costumer wants to add a contact-list? Now i have to create another table to store contacts info and repeat the columns name, lastname, etc, etc.

I had implemented this before and it worked perfectly.

The question is: Is my approach so bad? Is there any way to make it better?

1

Well, having occupation as part of Person isn't very well normalized. One person can have multiple occupations, so that should go in a different table or set of tables, depending on how normalized your schema is.

I am confused - how is a "bank name" an occupation? Or even related to a person?

I would note that while the theory of a Person table is sound, in practice it can get fairly interesting; for example, perhaps you start with U.S. SSNs. Then you get some Canadians with TINs (also U.S. 9 digit federally issued identifiers). And companies with TINs. Then you get some people with Mexican CURP values (18 character alphanumerics). Now you have a person with a CURP and a TIN both. Now you have a person with a Chinese identification number as well.

Let's not even get into truly international addresses :).

You may want to consider normalizing further, but definitely work out how a single human being with multiple roles (employee, vendor, customer) and multiple locations (different addresses or cities or regions or countries) and multiple ID's (TIN, SSN, etc.) is going to work.

Likewise with organizations - perhaps your food service vendor is also a customer, and then comes to work for you part-time.

EDITED TO ADD: I would also note that the "password" field horrifies me. Please read How to securely hash passwords? and replace the password field with fields for the plaintext random per-person salt, the PBKDF2/Bcrypt/Scrypt password hash, and ideally the # of iterations (so it's easy to increase).

  • When i wrote 'bank name' i forgot to write that was just an example but the the occupation table was truly a good idea. Thanks – Misters Feb 23 '14 at 13:59
1

I personally design tables the way you did here. It is a must to not store like your boss told to in this example. Just like you said, in the future you could save customers, dentists, users, contractors, and what not, and you will have duplicated columns for each of the entities. You could argue with them asking, how could you count all the people that reside in the database? Your approach would require few resources and efforts to do so. So, to my opinion and experience, your approach is correct.

  • I tried to warn then with not luck. Sooner or later they'll realize that i was right. – Misters Feb 23 '14 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.