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 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?

share|improve this question
add comment

1 Answer

No RDBMS system I know of supports mixing a data-type within a single column. So I think you've got two solutions:

  1. Separate tables - each user's list gets their own Database table. This would allow you the flexibility to customize the data type at the column level.
  2. Flexible data type in the "name" column. You could use a varchar or even a binary blob in the name column and allow the user to store whatever they want in that column. Then, at the application level, you could interpret that data in whatever format you like by, for instance, casting it to an integer.

It sounds to me, since you're hoping to store different types of data with different structures to it, that you should probably just be using separate tables.

share|improve this answer
    
Jeff Allen - Welcome to DBA.SE. I think you've got the start of a pretty good answer to this question - so +1. You might consider expanding the pros and cons of each approach or even just clarifying why you recommended option 1. Fully developed answers attract more up-votes and help you build your rep. –  Joel Brown Mar 25 '12 at 11:22
add comment

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.