[Librefm-discuss] Database schema... Problems in MySQL

[Pambo Paschalides]

Hi Guys,

I wonder about some aspects of the architecture of the database schema.

There are some strange cases with foreign keys. For example the DB table Similar_Artist, has 2 foreign keys of types VARCHAR, that reference the name field of table Artist, which is again of type VARCHAR. Don't you think it would be better that table Similar_Artist had 2 foreign keys of types BIGINT UNSIGNED that would reference the field id of table Artist ? In general referencing is much better done on the basis of IDs instead of strings for example.

Moreover, this creates a problem when the MySQL is used on the backend. MySQL has a limitation of 1000 bytes for the maximum key length. Depending on the encoding used, a character string even of small length can exceed this limit, and therefore can not be used as a key.

The same thing applies also for the artist foreign key of  table Tags...

I came upon these issues when trying to install GNUkebox in order to write some instructions on how the installation has to be done.

I think it's better to correct these database issues though before writing any tutorials. What's your opinions? Is there a reason why this dbase schema was used? Or shall I start making corrections?

Regards,

Pambo