Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 '11 at 9:20
    
Isn't this a valid use case for a NoSQL database like Mongo? –  pbarney Apr 3 at 17:15

3 Answers 3

up vote 2 down vote accepted

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.

share|improve this answer
    
This was pretty much where I was going to go as well. –  mrdenny Jul 27 '11 at 1:47

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 Ecommerce]
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.

share|improve this answer
    
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 '11 at 8:08
    
@Greg F - No, this won't work if you do not know the SubType ahead of time. –  Cape Cod Gunny Jul 26 '11 at 14:41

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

[Profiles]
UserID | name  | pass | email         | disabled
------------------------------------------------
1      | admin | admin| admin@test.com| NULL
2      | test  | test | test@test.com | 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.

share|improve this answer
1  
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 '13 at 12:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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