hi, i have a query that i hope someone can help me with.
i have a monitoring system that tracks the number of times that person clicks on a
particular adbanner on our site or in one of our newsletters. i member can choose
have a maximum of 16 interests selected.
so for example, i can say i'm interested in carbuying, the stockmarket and action
movies. at the moment i store the 16 interests as a bit bitfield,which gets decoded
on the server.
this system has worked very well for me, but now i have a few new requirements.
- the total number of possible interests that can be selected is now 150
- and i need store the users click rate and the number of times we have sent them a
newsletter relating to an interest or the number of times we have displayed an
adbanner related to their interest.
the long ineffient way would be
- to store 150 enum ("y", "n") fields to say that a user has selected a particular
interest
- then another 150 tinyint fields to store the response rate
- and another 150 tinyint fields to store the the count of times we have sent a
newsletter to them
this already results in 450 fields per record!
the best way i came up with was to store a number like "2_7" to represent 3 things,
the fact that a user has selected that interest, that the user has responded to 2 of
our 7 newsletters.
but this is still 150 fields per record.
we have about 250,000 members at the moment - this could get really out of hand very
quicky.
has anyone got any ideas on the best way to store this information ??
thanks for your help.
anna
