[Devel] database tables

Norman Brandinger norm at goes.com
Tue Dec 5 23:32:52 CET 2006


I am in agreement with Mike about the need to standardize the primary keys.

PGAdmin (PostgreSQL), for example will refuse to allow record 
modification if the table does not have a primary key.

Regards,
Norm


Mike Williams wrote:
> All,
>
> I think it would be a good idea to change all of the database tables created 
> by OpenSER to having the primary key be an unsigned auto_incremented int 
> named 'id'. The keys that are used now should become unique keys. 
>
> The reasons for this are these:
>
> 1. Protects against changes to database keys. If the primary key ever needed 
> to be changed for some reason, it wouldn't affect the unique identification 
> of each row.
>
> 2. Consistency. Some tables are using there own id names, when it would be 
> better to have just one standard one. Then, everyone would know that the 
> column id was an autoincremented unique int id for that table. As of now, in 
> some tables I find it hard to understand what the id names actually mean. For 
> instance, what does 'grp_id' mean in the table gw_grp? Is it a unique id, or 
> is it refering to the id of the 'grp' table? I would have to look it up to 
> find out. With 'id' there would be no ambiguity.
>
> 3. Greatly simplifies manual database work. Let's consider the lcr table:
>
> CREATE TABLE lcr (
>   prefix varchar(16) NOT NULL,
>   from_uri varchar(128) DEFAULT NULL,
>   grp_id INT UNSIGNED NOT NULL,
>   priority TINYINT UNSIGNED NOT NULL,
>   KEY (prefix),
>   KEY (from_uri),
>   KEY (grp_id)
> ) $TABLE_TYPE;
>
> As it is now, it would take a statement like this:
>
> DELETE FROM lcr WHERE prefix='A', from_uri='B', grp_id='C', priority='D'; 
>
> Just to delete one record. With a unique id, it becomes:
>
> DELETE FROM lcr WHERE id=X; 
>
> 4. Potentially could make using foreign key integrity easier. I'm not using 
> it, nor have I attempted to, so I can say this for sure, but I have a feeling 
> it would.
>
> 5. Makes my life developing OpenSER Administrator easier. Ruby on Rails does 
> not support the use of multiple keys. I'm sure it is not the only framework, 
> library, or program that has this problem. 
>
>
> ---------------------
>
>
> Tables that appear to need an 'id' field are:
>
> version, location, aliases, dbaliases, grp, domain, uri, trusted, speed_dial, 
> gw, lcr, pdt
>
> I would also recommend changing the current id field of these tables to 'id' 
> for consistency:
>
> re_grp (group_id), silo(mid), gw_grp (grp_id)
>
> ----------------------
>
> It's also possible that I'm completely wrong and that there are reasons it is 
> like the way it is now. Please feel free to comment.
>
> Thanks,
>
> Mike Williams
>
> _______________________________________________
> Devel mailing list
> Devel at openser.org
> http://openser.org/cgi-bin/mailman/listinfo/devel
>
>
>   




More information about the Devel mailing list