4

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.

2
  • Hi guys, thanks for your suggestions. We've discussed it internally and we've decided to go with the field definition/field value approach and limiting the size of the value field to varchar only and a relatively small size of varchar(150). Thanks again.
    – gfyans
    Jul 27, 2011 at 9:20
  • Isn't this a valid use case for a NoSQL database like Mongo?
    – pbarney
    Apr 3, 2014 at 17:15

3 Answers 3

3

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.

1
  • 1
    This was pretty much where I was going to go as well.
    – mrdenny
    Jul 27, 2011 at 1:47
3

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.

2
  • Hi CCG, will this structure work if I don't know what the SubType is though? The 2 examples I gave were just that, in theory anyone could register and create their own SubType.
    – gfyans
    Jul 26, 2011 at 8:08
  • @Greg F - No, this won't work if you do not know the SubType ahead of time. Jul 26, 2011 at 14:41
1

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.

1
  • 2
    For future readers: this can be a viable solution, but it needs careful planning if you want performance from this structure. A good reading on the topic can be found of Aaron Bertrand's blog (one of them, actually).
    – dezso
    Apr 16, 2013 at 12:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.