Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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:

Subscriber:

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?

share|improve this question

migrated from webapps.stackexchange.com Mar 23 '12 at 12:53

This question came from our site for power users of web applications.

add comment

1 Answer

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.

Cons

• 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.

Pros

• 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

Select
    rst.responseSetID,
    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
where 
    st.SubID = 1
    and vst.ListID = 1 
Group by
    rst.responseSetID

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)

share|improve this answer
    
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 '12 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. –  NeoModulus Mar 23 '12 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. –  NeoModulus Mar 23 '12 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. –  NeoModulus Mar 23 '12 at 14:54
    
Sorry i am new to this . Do you have skype (my skype id:foodil /msn :foodil@hotmail.com) 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 '12 at 14:59
show 1 more comment

Your Answer

 
discard

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