Morning. I'm having fun with DB design at the moment and I'm trying to work out the best way to store the data.
We're developing a site where each member has a profile that can be searched upon. The contents (i.e. fields) within this profile are not fixed and can vary from person to person (e.g. one person may have "field a" as an option whereas another has "field b"). I thought about doing this with a basic "link" style table: Member: Member_id Member_name Member_profile: Member_id Profile_type Profile_value Profile_type: Profile_type_id Profile_type_desc (this is a very simplified version -- the "real" version has fields that are text boxes, checkboxes, select boxes etc, but this is an example). Doing a search would then involve one inner joins to the member_profile table for every field that was searched upon. I've done something like this before and it's flexible enough for what I wanted, but doing a brief test -- searching through 40,000 member records with only two "member_profile" fields takes about 1.2 seconds on average (for the query). Considering we could be looking at 100,000+ members with up to 20 fields searched upon at one time, the query time would be waaaay too high... Another option is to simply have one central (profile) table with a column for each possible field that the user could fill out -- searching would be fast (assuming indexes were sensible!) but we could end up with one very large, unmanagable table: what would happen when (not if) we wanted to add a new possible field? Any suggestions appreciated. Tim. ------------------------------------------------------- RAWNET LTD - Internet, New Media and ebusiness Gurus. Visit our new website at http://www.rawnet.com for more information about our company, or call us free anytime on 0800 294 24 24. ------------------------------------------------------- Tim Blair Web Application Engineer, Rawnet Limited Direct Phone : +44 (0) 1344 393 441 Switchboard : +44 (0) 1344 393 040 ------------------------------------------------------- This message may contain information which is legally privileged and/or confidential. If you are not the intended recipient, you are hereby notified that any unauthorised disclosure, copying, distribution or use of this information is strictly prohibited. Such notification notwithstanding, any comments, opinions, information or conclusions expressed in this message are those of the originator, not of rawnet limited, unless otherwise explicitly and independently indicated by an authorised representative of rawnet limited. ------------------------------------------------------- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
