Link to home
Start Free TrialLog in
Avatar of saturation
saturation

asked on

Best practices for data

I am setting up some database tables where the user will sign up on an online form, then they will also get the chance to register 10 other e-mail addresses.  I am wondering if I should put all of the regular form data and the other 10 email addresses on the same table or create another table for the 10 e-mail addresses and join them to the main registration table.  Which way is best?
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I agree with chapmandew, creating an email address table will be better.

If you put them on a single table, then you would need 10 email address columns. Many of these will only have null values in it since most of the users don't have 10 email addresses (i think). It will be easier to handle the records with a separate table.

e.g.
If you want to count the number of email address each user has entered,

for only 1 table:
SELECT UserID,
             CASE WHEN email1 IS NULL THEN 0 ELSE 1 END + CASE WHEN email2 IS NULL THEN 0 ELSE 1 END + ...
FROM Registration
GROUP BY UserID

for separate tables:
SELECT UserID,COUNT(*)
FROM Registration R LEFT JOIN Emails E ON (R.UserID=E.UserID)
GROUP BY UserID
What could be the data length and number of rows for next 3 years?