[Devel] database tables
Juha Heinanen
jh at tutpro.com
Wed Dec 6 06:39:36 CET 2006
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
More information about the Devel
mailing list