[Devel] database tables

[Christian Schlatter]

mysql-cluster needs an integer primary key per table, so if there is 
none available it creates one implicitly. So at least for mysql-cluster, 
adding an ID field to each table does not increase storage space. I also 
agree with all of Mike's points, especially that more and more web 
application frameworks expect an ID key per table.

Christian


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

-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/x-pkcs7-signature
Size: 1951 bytes
Desc: S/MIME Cryptographic Signature
Url : http://openser.org/pipermail/devel/attachments/20061206/07f88e1c/smime.bin