I'm currently developing an bulk-email campaign manager that will allow customers to upload as much information as they would like on their subscribers.
For example, I have a basic profile that is available to everyone:
- First name
- Last name
- Email address (etc.)
This is stored in my SubscriberProfile table, and all is good.
However, I want my users to have the ability to create custom fields for subscriber information.
For example, User A might run an e-commerce site and will have the following custom fields:
- Address Line 1 (varchar(100))
- Address Line 2 (varchar(100))
- City (varchar(100))
- Postcode (varchar(7))
User B might run a cinema and have the following custom fields:
- Favourite cinema (varchar(50))
- Favourite genre (varchar(50))
The data could be indexed if the users wishes to use them for searching their subscribers or to create sub-groups of their main subscriber list.
How would I best structure my tables?
I was thinking initially of having 1 table that holds the custom field definition (FieldID, UserID, FieldName) and 1 table that holds all the custom field values (FieldValueID, FieldID, VarcharValue, IntValue, BitValue, etc.) but this won't allow me to index 1 customer's set of data without indexing everyone else's. It will also mean I'll have null values for all but one column per row. Seems a waste.
Another thought I had is to create a table per custom profile per user. This would allow me to index data, but what if 1000 people register for the site? I'm going to have 1000 tables to hold the custom profile data, is that good practice?
Am I approaching this wrong, is there a better way to do this?