[Serusers] Help for mysql to ser 2.0.0. rc1

[SIP]

The entire database structure of SER 2.0 has changed somewhat drastically.

The subscriber table no longer exists. ALL user attributes are now 
stored in the usr_attrs table in classic single attribute/value pair model.

I have somewhat mixed impressions about the new schema. It has some 
logical points for storing system code, but for DB management, it's a 
bit nightmarish. Taking the sheer strength of a complex, well-optimised 
relational database and essentially breaking it down into container for 
a bunch of attribute value pairs is... well... painful to look at. ;)

If you want to learn more about the new DB structure, it's here:  
http://www.iptel.org/files/db-structure-0.10.pdf

On the positive (?) side, the new method of storing usr_attrs allows for 
a dynamic level of user attributes without adjusting the table itself.  
On the negative side, it makes for a complete impossibility for having 
more than the two predefined (attribute/value) primary keys, or for 
checking any sort of referential integrity against anything other than 
an attribute or a value.

Code you write surrounding the new DB (and I know there's this general 
feeling that the DB is for the back-end of SER and the back-end of SER 
ONLY, and that any sort of front-end code should go in a different DB, 
but that's just 100% silly) will have to create joins and other complex 
structures in order to grasp most of the necessary data for a user, as 
well as sort through multiple rows of data for each user instead of 
gathering data in a tidy little row. You will also have to keep careful 
track of the attributes you've allotted somewhere, as otherwise, your 
code is going to be a horrific mess if you ever DO change anything, 
since it won't be just a simple table structure change, it will be a 
change in the way you handle all the data grabbed from the table.

Expect your usr_attrs table to grow FAR faster than your subscriber 
table did, and plan for space accordingly. Also expect to redo any 
optimisations you've written for how you sort through the code, as there 
will be a considerably larger amount of code to sort through per user, 
so optimisation will be more difficult on the front-end side.

On the bright side, expect to be able to handle added features with much 
greater ease if you code your front-end software correctly. No longer 
will you have to decide whether a landline phone number or privacy 
preference gets tied to a subscriber in the subscriber table, or gets 
tied to a subscriber in the user_preferences table (although, to be 
honest, I don't think anyone ever did that). It will all go in one 
table, referenced by a unique (although not primary-keyed (?)) userID.

On the not so bright side, expect migration to SER 2.0 from your old DB 
scheme and front-end codebase to be a nightmare and several halves. :)

Liu Wenlong wrote:
> why there is not a subscriber table in my_create.sql of ser 2.0.0 rc1? 
> AND how can I create it?
>  
> thanks
>
> -- 
> aRES iN aNYWhere
> ------------------------------------------------------------------------
>
> _______________________________________________
> Serusers mailing list
> Serusers at lists.iptel.org
> http://lists.iptel.org/mailman/listinfo/serusers
>