Database design with child bit values in columns or rows in SQL Server

I am creating a form that has about 70 checkboxes to store users interests. I am debating on what would be the most efficient way to design the SQL tables. I have a user table that stores the user information:

UserTable
UserID
UserFname
UserLname
UserA开发者_开发知识库ddress
UserHomePhone
UserCellPhone

I am not sure if I should create one table with a bunch of bit columns set to 0 or 1 depending on the user's interest, like so:

Table1
InterestID
Aerobics
Bridge
Camping
Fishing
Bowling
UserID

Or should I create two tables like so:

Table 1
InterestID
InterestName

Table 2
ID
UserID
InterestID

Please advise the most efficient way to handle this situation even if it is not any of these two options.

---

I think the second. For the first setup Id wouldn't even make much sense. What it will refer to? A set of 1s and 0s?

The second setup will make it easy to query all interests for users and other way around by ID only. You'd have two indexes for speeding up, vs either having no indexes or too many in the first scenario.

And you don't need an ID for the second table that ties users ID and interest ID - just create a primary key on both columns. The ID is useless. You don't need to list same interest ID for the same user ID twice, so there shouldn't be any conflicts with that primary key setup.

I might be wrong though and maybe someone else have different ideas.

---

I strongly recommend the second way. Believe me, you will thank yourself later. Some aspects of this second way are a little harder to get rowsets that people typically think of (with the column names of interests across the top) but I guarantee that all around you will have three times an easier life making the interests in rows instead of columns.

What if you want to add an interest? What if you want to count the interests of users? These tasks become super hard if you use the first method.

I also second what AR said about getting rid of the ID from Table2.

Note that if this is a web site you may not have to return the data pivoted to have the interests as column names. If you loop through the recordset yourself you can draw the items you need (with a little cleverness) and skip the pivot entirely.

---

Use the second way.

The table is a collection of user interests. As long as the value is the same for each interest (Checked/Unchecked) then this makes good sense.

Table 1 InterestID Interest

Table 2 UserID InterestID