Database is not normalized - any reason for that?

[TwizzyDizzy]

Hi folks,

unfortunately your database is not normalized (https://en.wikipedia.org/wiki/Database_normalization).

For example, the song table holds highly redundant information about artists, bands and genres:

mysql> select id,title,album,artist,band,genre from songs;
+------+----------------+-----------------------+-----------------------+-----------------------+-------+
| id   | title          | album                 | artist                | band                  | genre |
+------+----------------+-----------------------+-----------------------+-----------------------+-------+
| 1031 | Inside outside | Psychedelic Orchestra | Psychedelic Orchestra | Psychedelic Orchestra | NULL  |
| 1032 | Easy tiger     | Psychedelic Orchestra | Psychedelic Orchestra | Psychedelic Orchestra | NULL  |
| 1033 | Karma Dub      | Psychedelic Orchestra | Psychedelic Orchestra | Psychedelic Orchestra | NULL  |
| 1034 | Sun            | Psychedelic Orchestra | Psychedelic Orchestra | Psychedelic Orchestra | NULL  |
+------+----------------+-----------------------+-----------------------+-----------------------+-------+
4 rows in set (0.00 sec)

They should all be transformed to seperate tables and should be referenced via foreign keys.

This is, by no means, a complete analysis of your database structure - I first wanted to check if there is some special reason for the current state of affairs.

Given that a sonerezh instance may hold millions and millions of database records, normalization seems strongly advisable.

Cheers Thomas

[lGuillaume124]

You are quite right. We would like to provide an automatic update system first to facilitate all these major changes.

[TwizzyDizzy]

Hi Guillaume,

ah, yes, I guess some kind of incremental database upgrade mechanism would be crucial to an easy enough upgrade process.

[lGuillaume124]

To be honest when we started Sonerezh we began to think the database architecture in the way you said:

• One table for the bands
• One table for the albums
• One table for the artists
• Etc...

And intermediates tables to join them all.

But as we progressed in the project, we quickly realized that MySQL queries would become too complex to maintain, despite the powerfull CakePHP ORM. That's why the database schema is what it is today :)

And I agree with you, Sonerezh is not designed yet to manage millions and millions records.

[zertrin]

I understand why the database is like it is today. However I also think that is not a really great choice.

I don't know for sure that the fact that the database isn't normalized is the reason, but even with few numbers of songs (see below), I find that the interface is really slow to show things. I have a dedicated server that can transcode and stream high quality video in real time so I don't think the slowness comes from the server at all. I highly suspect the fact that the database isn't normalized + a lack of optimization in the SQL queries (a lot of sequential scans of tables not normalized, followed by sorting and grouping can't be quick). Of course, in order to be able to check on that, we would need some possibility of getting debug information and statistics about the database queries.

For reference, my current stats, very far from millions of records:

735 Artists 1036 Albums 4655 Songs

[lGuillaume124]

Hi @zertrin

I agree with you. The database side needs to be completely rewritten... we were young, innocent and newbies when we designed it :D

We'd like to work hard on it, but both of us are very busy :/