1:1 or single table

[veledrom]

Hi,

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?

Thanks

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.

[Philippe.Lahaie]

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! :)

[adam_k]

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).

[veledrom]

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

Thansk guys