Hey folks, So in my current project, I've got dupes (for each client) of one DB table, up to a point. Each client DB table has zero, one or more distinct fields added on (customization thing).
Data from forms go into these fields. In some cases, multiple bits of data go into one field - say a form field is a checkbox, or a multi-select field, where the user can select multiple items. I currently store all the data bits into these DB fields (varchar type) by massaging the data into a pipe-delimited list, with leading/trailing pipes, i.e. "|1|2|3|". This way when doing searches, I can do WHERE fieldname LIKE '%|1|%' instead of WHERE fieldname LIKE '%1%' and maybe pick up a record that has "11" as a data bit in that field. My main question, from a DB design standpoint, is: Is it better to have the single varchar field in a given table like I'm doing, or is it better to create a seperate DB table to contain these data bits individually? i.e. ID FieldID Data 101 999 1 102 999 2 103 999 3 I'm not so concerned about my code and getting the data in/out - I could do it either way with equal ease, I figure - I'm asking more from a best-practices standpoint, and a database-efficiency and size standpoint. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2548 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
