I second this. Mind you, I do this grudgingly, mainly 'cos everytime the db structure changes, I have to go through and migrate everything (and openser is pretty deeply embedded throughout our business processes)
cheers ----- Original Message ----- From: Norman Brandinger <[EMAIL PROTECTED]> To: Mike Williams <[EMAIL PROTECTED]> Cc: [email protected] Sent: Tuesday, December 5, 2006 4:32:52 PM GMT-0600 Subject: Re: [Devel] database tables 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 > [email protected] > http://openser.org/cgi-bin/mailman/listinfo/devel > > > _______________________________________________ Devel mailing list [email protected] http://openser.org/cgi-bin/mailman/listinfo/devel -- ******************************************* Mahesh Paolini-Subramanya (703) 386-1500 x9100 CTO [EMAIL PROTECTED] Aptela, Inc. http://www.aptela.com "Aptela: How Business Answers The Call" ******************************************* _______________________________________________ Devel mailing list [email protected] http://openser.org/cgi-bin/mailman/listinfo/devel
