adding columns to a populated table - bad practice?

fazbob

Hey all,

I got a scenario where i need to design a table for userProfiles. Currently the profile stores userID's (foreign key - there's a user table which stores the userIDs), and against each userID i need to store the number of times the user has accessed each of the available services offered.

So i have something like:-

TableName = userProfiles

Table Columns:

userID int
newsService int
sportsService int
musicService int

Those are not all the real services on the table-column list - i currently have about 10 services, and the problem is that the services will be growing continously (sometimes several new services may come up in a month) - thus i will need to add new columns for the new services whenever they come up (if im to go with the design shown above)

My concern is this - is adding new columns to a table bad practice? (a very large table - with many columns and many rows)

I know it may be bad to add new columns if the data in the new columns will be null values, but i will make sure that the default for any new column created is ZERO.

So, is there any bad thing about adding new columns to a table that already contains data as i go along?

Thanks

laserlight

There is a many-many relationship between userProfiles and services. You should have the services in a separate table, and then create a third table to store the relations.

Table: userProfiles
userID int

Table: services
serviceID

Table: userProfiles_services
ID
userID
serviceID

So, is there any bad thing about adding new columns to a table that already contains data as i go along?

Not necessarily (in my opinion as a non-DBA), but you might want to read the PHP Builder article on Database Normalization and Design Techniques.

napper

I posted this in your other thread:

I would not do that. My suggestion is have a services table with 3 columns. This makes the tables very relational and keeps overhead down.

For example:

Table Columns:

userID int
serviceCode int
accessedTimes int

Have another table called Services (or similar) with the following

serviceCode int (PK)
serviceDescription

Have have the data like
1 - news
2 - sports
3 - music

You would then build an index (or foreign key relationship if your db supports it) from the Service table to your userProfiles table on serviceCode. Alternatively to keep down on lookups, you can insert a row into a tabe named like servicesAccessed and make it

userID int
serviceCode int

Then just issue SELECT Count(userID) As Cnt, userID, serviceCode FROM servicesAccessed WHERE userID=1 GROUP BY userID, serviceCode

This would keep from index lookups on every hit, but you would get hit for a index fill every time a row was inserted into the other table. It depends on howyou feel comfortable.