Access Restriction with SQL / ASP

[CrimsonDiva]

Hi!

I'm trying to set up subscription information for a mailing list that i'm creating. A user can decide from up to 10 choices of the types of e-mail they'd like to receive.

My question is how should I represent this in SQL Server 2000? I have a user table with information about the user and a user id as the key. I imagine I will create another table with the user_id as the primary key, but that's as far as I've gotten. I'm not sure if I should create a column/field for each of the 10 different email types (as bit fields) OR if I should just have one other column with a delimited set of all the choices the user has access to (i.e. choice1|choice2|choice6|)

Please help!
Diva

 

[SQLSister]

NO to both ideas. Both ideas are very bad from a relational database perspective and the first idea makes the database hard to maintain as the types change. In regards to the second idea, the FIRST rule of database design is NEVER store more than one piece of information in a field.

You should set up a related table that has as columns user_ID and Email_Type. Then if someone selects one email type they will have one record and if someome selcts all ten types they will have ten records.

 

[CrimsonDiva]

Thanks SQLSister,

I thought of that option before, but thought it could be too cumbersome since our user database has at least 1300 records. I assume you're saying that this shouldn't be a problem with the new table. Is there a limit on the number of records any table can have?

Thanks for all of your help. I'll work on this today!

Diva

 

[SQLSister]

SQL databases can support millions of records.