Table structure for many custom profiles

Question

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?

Thanks, Greg.

Answer 1

It always pains me to suggest it but theres a reasonable case for making use of an XML column here.

Create appropriate tables to handle commonly used fields i.e. name, address, email, age, gender etc. I'd be tempted to store the custom fieldsets as an XML column. Once the number of subscribers they are emailing reaches a level where performance may become a problem, trigger a notification to the DBA so they can review the custom fieldsets created by your users and see if there is justification for moving them out of XML columns to defined sub-type tables.

Obviously you end up with two code paths for handling XML and sub-type definitions but you retain flexibility while having a process for dealing with the potential performance implications.

If SQL2008 was an option, I'd consider making use of sparse columns for the custom fields (generic definitions, along the lines you mentioned) and combine with filtered indexes for the larger customers.

Answer 2

You can use what is called a SuperType / Subtype data structure. You have one SuperType table that contains information that is the same for all subscribers:

[SuperType]
SubscriberId
FirstName
LastName
Email

Create a SubType table for Ecommerce

[SubType Ecommerce]
SubscriberId (Foreign Key to SuperType)
Address1
Address2
City
State
Zip

Create a SubType table for Cinema

[SubType Cinema]
SubscriberId (Foreign Key to SuperType)
FavoriteCinema
FavoriteGenre

[SuperType] + [SubType Ecommerce] = EcommerceSubscribers
[SuperType] + [SubType Cinema]    = CinemaSubscribers
[SuperType] + [SubType Category3] = Category3Subscribers
[SuperType] + [SubType Category4] = Category4Subscribers

This lets you build new subtype relationships as the need arises.

Answer 3

I suggest to use mapping between tables to create very flexible profiles.

[Profiles]
UserID | name  | pass | email         | disabled
------------------------------------------------
1      | admin | admin| [email protected]| NULL
2      | test  | test | [email protected] | spammer

[Fields]
FieldID| name    | description | type    | disabled
-------------------------------------------------
1      | city    | City        | text    | NULL
2      | zip     | ZIP         | text    | NULL
3      | address1| Address 1   | textarea| NULL

[map_Profiles_Fields]
UID  | UserID | FieldID | Value
-------------------------------------------
1    | 1      | 1       | Moscow
2    | 2      | 1       | Los Angeles
3    | 1      | 2       | 123123
4    | 2      | 3       | 1805, Montana St

etc

Now user 'admin' has city 'Moscow' and ZIP '123123' in his profile. You can create unlimited number of users and fields and map them.