[Devel] database tables

[Bogdan-Andrei Iancu]

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
>
>  
>