Table structure help

[primagic]

Really just looking for some advice.

I have a table with information regarding Host families.

I have another table with information regarding Agents (Businesses). These tables have different fields however two fields that are in both tables are username & password.

Should I store the username & password for each recrod in a seperate table or keep the structure as is?

 

[gmmastros]

I would be tempted to create another table for this, but the correct answer is difficult to say.

If you have a lot of code built up around your existing structure, then you need to weigh the cost vs. benefits. The cost would be development time. The benefit would be a central location for user registrations.

Things to consider before you change anything:

Can the same username exist is both tables?
Do you have any logic built up around which table a username is found?
Are you currently encrypting the password column?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[primagic]

I have no logic built yet just starting to develop the database and it is not likely that a username will appear in both tables.

I am going to use the email address as username so I want it to be easier enough when they change their email address this will change their username

 

[gmmastros]

In that case, I would encourage you to use a separate table for username and password. This table should have UserId (int). The userid should be in each of the other tables (host and agents).

I say this because it is likely that requirements will change and someone will want to store additional information about each user. Stuff that's common to host and agents, like Full Name, Phone Number, Last Login Date/Time, etc... It will be a lot easier to change functionality later if you have a User table where you can make the db changes in just one place.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom