[Devel] [ openser-Feature Requests-1611616 ] use autoincremented int for the primary key of db tables

[SourceForge.net]

Feature Requests item #1611616, was opened at 2006-12-08 11:36
Message generated for change (Tracker Item Submitted) made by Item Submitter
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=743023&aid=1611616&group_id=139143

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: None
Group: None
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: Mike Williams (mikebwilliams)
Assigned to: Nobody/Anonymous (nobody)
Summary: use autoincremented int for the primary key of db tables

Initial Comment:
COPY OF ORIGINAL EMAIL SUGGESTION:

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

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

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=743023&aid=1611616&group_id=139143