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 <[EMAIL PROTECTED]>) when dealling with a
mysql bug
related to the primary key size (see
https://sourceforge.net/tracker/?func=detail&atid=743020&aid=1605410&grou
p_ 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@openser.org
http://openser.org/cgi-bin/mailman/listinfo/devel
