I want to know what would be your preferences in this case. I'll have a login and sign up process and want to develop its database tables.

  • 1:1 version needs JOIN everytime when loging in, T-SQL when signin up new user.
  • Single table version doesn't need two bits above.

What would proper database designer go for?


1:1 relationship
users { id (PK), name, surname }
login { id (PK), username, password, salt, usersid (FK) }

single table instead
login { id (PK), name, surname, username, password, salt }

Note: Don't worry about adding new columns to the tables. I'm more interested in your ERD&structure.

I have always developped user tables in the single table pattern, but ive never had to do one in a real security situation, only in college projects....

I do have do create one for a real project in the following months and i dont believe it would make much difference security wise to separate the credentials in a different table.

But if i am wrong i would love to know why! :)

I don't think it makes a difference. Worse case scenario go with single table and use it only through views to control the ammount of info available.

I always take into account the sensitivity of data when designing a db. In your example it doesn't matter (unless you are treating names as sensitive data). Think that you were holding credit card info, bank accounts, address & phone numbers, digital signatures, e-mails and medical info. Would you risk "sharing" all this in 1 SQL injection?
Another parameter to take into consideration is size/number of times you'll need it (ie user photo: nice to have but useless when logging in).

commented: agree with your suggestion based on the scenario. +4

Sound like I'll stick with one table cos nothing special about it.

Thansk guys

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.