I am building a application that allow different user to store the subscriber information

  • To store the subscriber information , the user first create a list For each list, there is a ListID.

Subscriber may have different attribute : email phone fax ....

For each list, their setting is different , so a require_attribute table is introduced. It is a bridge between subscriber and List

That store Listid, subid, attribute, datatype

That means the system have a lot of list, each user have their own list, and the list have different attribute, some list have email , phone , some may have phone, address, name mail.. And the datatype is different, some may use 'name' as integer, some may use 'name' as varchar

  • attribute means email phone, it is to define for which list have which subscriber attribute

  • datatype means for each attribute, what is its datatype

    Table :subscriber :        
    Field :subid , name,email
    Table :Require Attribute:
    Field : Listid ,subid , attribute, datatype

The attribute here is {name, email}

So a simple data is

Subscriber: 1 , MYname, Myemail

Require Attribute :

 Listid , 1 , 'email', 'intger'

 Listid , 1 , 'name', 'varchar'

I found that this kind of storage is too complex to handle with, Since the subscriber is share to everybody, so if a person want to change the datatype of name, it will also affect the data of the other user.

Simple error situation:


list1,  Subscriber 1 , name1, email1

list2, Subscriber 2  , name2 , email2

Require Attribute :

     List1 , Subscriber 1 , 'email', 'varchar',

     List1 , Subscriber 1  , 'name', 'varchar', 

     Listid , Subscriber 2 , 'email', 'varchar', 

     Listid , Subscriber 2, 'name', 'integer', 

if user B change the data type of name in require attribute from varchar to integer, it cause a problem. because list 1 is own by user A , he want the datatype is varchar, but list 2 is won by user B, he want the datatype to be integer

So how can I redesign the structure?

1 Answer 1


You can solve this problem using something I have called a vertical table solution in the past. There are both pros and cons to this solution. I can provide a sample database upon request.


• Loss of horizontal structure makes queries difficult to write and slower to perform. The difficulty in writing queries can be helped with views. Views introduce their own set of issues as well.

• Data integrity becomes more difficult. Since you are defining the datatype abstractly in a table and storing as as the most common type, usually some form of text field, it puts a burden on verifying the data on input. Previously I have investigated using foreign keys to insure valid data but have not implemented a solution.

• Does not support 'many to one' relationships well, but it can be done.


• Customer is able to define their own data definition.

• Change in definition does not adversely affect other clients. However if a client changes their own datatype or meaning there can be issues.

• Can become 'brittle' once a list is defined and data is collected.

Sample Query

    Max(case when lvdt.CustomerFieldName = 'Email' then vst.InputValue end) as Email,
    Max(case when lvdt.CustomerFieldName = 'Name' then vst.InputValue end) as Name
From SubscriberTable st
    join ListDefinitionTable ldt on 
        st.SubID  = ldt.SubID 
    join VerticalStorageTable vst on 
        ldt.ListID = vst.ListID
    join ListValueDefinitionTable lvdt on 
        vst.ListDefintionID  = lvdt.ListDefinitionID 
    join AttributeDefinition ad on
        lvdt.AttributeTypeID = ad.AttributeTypeID
    Join ResponseSetTable rst on
        vst.ResponseSetID = rst.ResponseSetID
    st.SubID = 1
    and vst.ListID = 1 
Group by

Subscriber Table

• SubID (ID) (PK)

• CustomerName (Text)

Attribute Definition

• AttributeTypeID (ID) (PK)

• AttributeType (Text)

List Definition Table

• ListID (ID) (PK)

• SubID (ID)

• CustomerListName (Text)

List Value Definition Table

• ListDefinitonID (ID) (PK)

• ListID (ID)

• AttributeTypeID (ID)

• CustomerFieldName (Text)

Response Set Table

• ResponseSetID (ID) (PK)

• SubID (ID)

• ListID (ID)

• TimeInput (DateTime) (Default – GetDate())

Vertical Storage Table

• InputID (ID) (PK)

• ResponseSetID (ID)

• ListID (ID)

• ListDefinitonID (ID)

• InputValue (Text)

  • However if a client changes their own datatype or meaning there can be issues.? That means the user can not select the data type ?
    – Leo Chan
    Mar 23, 2012 at 14:47
  • This would only be an issue after data has been collected for a period of time. A customer defines a list with email (text), phone(text). Data is collected for a period of time and validated against the type. Later the customer changes the phone(text) to phone(number). This does not cause immediate issues. However there could be data collected and verified to be (text) that will not be able to be cast to (number). This would require data cleaning, but it can be fixed.
    – John Ruf
    Mar 23, 2012 at 14:53
  • If the customer changes email(text) to address(text) after a period of data collection this would cause immediate logical issues. What was once collected as email is now viewed as an address.
    – John Ruf
    Mar 23, 2012 at 14:54
  • With business rules and setting expectation with the customer the risk of both of these issues can be mitigated and potentially eliminated.
    – John Ruf
    Mar 23, 2012 at 14:54
  • Sorry i am new to this . Do you have skype (my skype id:foodil /msn :[email protected]) or something so that it is easier for me to show you. Would you mind take a look in my sql table and provide some suggestion? (that is easy, not take you much time)
    – Leo Chan
    Mar 23, 2012 at 14:59

Your Answer

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