Re: [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,


The reasons for this are these:





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:


Just to delete one record. With a unique id, it becomes:





---------------------


Tables that appear to need an 'id' field are:





re_grp (group_id), silo(mid), gw_grp (grp_id)

----------------------



Thanks,

Mike Williams

_______________________________________________
Devel mailing list
Devel@openser.org
http://openser.org/cgi-bin/mailman/listinfo/devel

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. 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. DELETE FROM lcr WHERE prefix='A', from_uri='B', grp_id='C', priority='D'; 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. 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: 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.

smime.p7s
Description: S/MIME Cryptographic Signature

_______________________________________________
Devel mailing list
Devel@openser.org
http://openser.org/cgi-bin/mailman/listinfo/devel