Data base structure of a subscriber list

Posted by foodil on Programmers See other posts from Programmers or by foodil
Published on 2012-03-23T11:41:12Z Indexed on 2012/03/23 17:40 UTC
Read the original article Hit count: 273

Filed under:
|

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 too handle with, Since the subscriber is share to every body, 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. becasue 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?

© Programmers or respective owner

Related posts about database

Related posts about mysql