[Devel] database tables

[Mike Williams]

All,

Should I submit a feature request then? I'm willing to do some work. The good 
thing about this kind of upgrade is that it doesn't really affect the rest of 
the table data at all, and I would guess it doesn't actually affect the code 
of openser either (Besides the database creation scripts). 

Mike

On Wednesday 06 December 2006 04:32, Bogdan-Andrei Iancu wrote:
> Hi everybody,
>
> couple of ideas from my side on this topic:
>
> 1) I'm not a DB expert, so most of the ideas are based on second hand
> information :)
>
> 2) RDBMS theory (or concepts) suggests that every table should have
> field ID as unique number getting from auto increment sequence (state
> by  Khalukhin Alex <khalukhin at gmail.com>) when dealling with a mysql bug
> related to the primary key size (see
> https://sourceforge.net/tracker/?func=detail&atid=743020&aid=1605410&group_
>id=139143)
>
> 3) I tent to agree with Mike - when is about writing interfaces or
> applications for provisioning the DB, having a one column primary key
> helps a lot - especially in correlating different tables, cross
> references, easy identification of records, etc
>
> 4) it is possible to help also inside openser - if you have an operation
> involving 2 queries (like a select and delete), a smaller amount of
> information is required to be stored (from select) in order to trigger
> the delete.
>
> 5) I had some time ago a discussion with a senior mysql consultant and
> he strongly advised to use auto increment ints as primary keys. I do not
> remember the arguments behind (how the tables is hashed, how the hash is
> balanced, how efficient data is locate, etc), as , again, I'm not to
> much in DB stuff, but I recall the conclusion.
>
> 6) I already started changing some tables to have this kind of PK -
> acc,missed_call table.
>
> any other input (as technical arguments) is welcomed.
>
> regards,
> bogdan
>
> Juha Heinanen wrote:
> >Mike Williams writes:
> > > 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.
> >
> >mike,
> >
> >i have not seen much use for auto-increment id keys in implementing an
> >openser management system.  if there is no really good use case, an
> >extra key just adds to table size.
> >
> > > 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.
> >
> >grp_id of gw_grp table is NOT an auto-increment unique key.  from README
> >file:
> >
> >   Each gateway belongs to a gateway group either alone or among
> >   other gateways. All gateways in a group share the same
> >   priority.
> >   ...
> >   Table lcr contains prefix of user part of Request-URI, From
> >   URI, gateway group id, and priority.
> >   ...
> >   In addition to gw and lcr tables there is third table gw_grp
> >   that is used to associate names with gateway group ids.
> >
> > > 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;
> >
> >i don't consider this a big deal when the query is created automatically
> >by management system.
> >
> >in summary, although i don't see any urgent need, i would not oppose
> >adding a an auto-increment key to tables that tend to have only a small
> >number of rows.  but if there are tables that can be big and that
> >currently don't have such a key, i would carefully consider if it really
> >needed.
> >
> >-- juha
> >
> >
> >
> >_______________________________________________
> >Devel mailing list
> >Devel at openser.org
> >http://openser.org/cgi-bin/mailman/listinfo/devel