Database Cleanup

[DaSourcerer]

Our database schema leaves a lot to be desired:

• [ ] Tables need to be renamed from e.g. YBoardPost to post. We should see to it that models reflect this as well and allow for a prefix applied to them by configuration. Easiest way to achieve this is by eliminating all tableName() methods.
• [ ] Normalization. A lot of tables have bogus fields that can be evaluated through relations. e.g. a Post knows to which Thread it belongs. But it also knows its relation to a forum, although said relation is already set through the Thread. This furthermore contains:
  • [ ] Restoring natural primary keys. A good example is the YBoardVote table: It's consisting of three separately indexed fields, while it should only be a composite primary key over member_id and choice_id;
  • [ ] using more appropriate field types. A TINYINT(4) isn't exactly a clever choice for a field that is only ever used to contain a boolean value;
  • [ ] renaming things for a more expressive and consistent nomenclatura. Examples:
  • forums → boards
  • topics → threads
  • choice → poll_option
  • logtopic → user_has_read
  • [ ] Consolidate or break up tables. Users and Members got a 1:1 relation, yet they aren't identical. A member is a profile, really. It should also contain many of the fields that are now part of the User table. In general, we should not rely at all on what a User provides as they are meant to be interchangeable between applications. OTOH, there's a lot of fields in the Post table, keeping track of the last change. These should rather go into their own table to allow for change tracking (w/o versioning). The "profile" URLs in the Members table are worthy for an EAV setup, IMHO;
• [ ] Write out tables to disk. Global settings and ranks are much more of a configuration thing and should be reached that way: Through the configuration, not the database. Per-user settings are a different story, though;
• [ ] eliminate counting and statistics fields. These are a pain and prone to cause all kind of havoc, including but not limited to synchronization issues. There are some performance implications so we should proceed with special care here;
• [ ] remove bogus keys, look for missing ones. Also: Remove all indexes that are left prefixes of others. At least in MySQL/MariaDb, these are totally bogus.

[mtangoo]

I will let you do this ad have my opinion once you fix the matters but here are my few notes:

Tables need to be renamed from e.g. YBoardPost to post. We should see to it that models reflect this as well and allow for a prefix applied to them by configuration. Easiest way to achieve this is by eliminating all tableName() methods. Can you explain where the bolded part will be retrieved from? Configuration table? And how will we apply it to th model? I mean will it be installation choice or just a fixed prefix?

logtopic → user_has_read

I have problem with this table (I even raised it to Bbii developer). With time and increased members it grows to huge monster. So I think we need better way to track users.

Write out tables to disk. Global settings and ranks are much more of a configuration thing and should be reached that way: Through the configuration, not the database. Per-user settings are a different story, though;

Are we going to have Global settings Table? I'm thinking that we should and then make application component called settings (preferences or whatever) that will help us access them application-wise. As per user settings, am not sure if you have any suggestion on how to store them.

As for User table, I believe letting user have his own login system is a must. That means Some user information pertaining to authentication, have to be decoupled from user profile. That will enable user to implement his own version of login system yet be able to seamlessly integrate into forum. I have no specific design in my head right now and would like to hear what you think of it.

[mtangoo]

@DaSourcerer what do you think of my comments? I think we need to push this too

[mtangoo]

I will take over the task of Tables need to be renamed from e.g. YBoardPost to post. We should see to it that models reflect this as well and allow for a prefix applied to them by configuration. Easiest way to achieve this is by eliminating all tableName() methods.

This will affect Model names as well as table names. So I will make Upgrade migrations

Do you have any inputs @DaSourcerer as to any alternative to this?

[mtangoo]

I will take on this this week