{"action":"created","issue":{"url":"https://api.github.com/repos/hosannahighertech/yii2-yiiboard/issues/8","labels_url":"https://api.github.com/repos/hosannahighertech/yii2-yiiboard/issues/8/labels{/name}","comments_url":"https://api.github.com/repos/hosannahighertech/yii2-yiiboard/issues/8/comments","events_url":"https://api.github.com/repos/hosannahighertech/yii2-yiiboard/issues/8/events","html_url":"https://github.com/hosannahighertech/yii2-yiiboard/issues/8","id":72253438,"number":8,"title":"Database Cleanup","user":{"login":"DaSourcerer","id":1443215,"avatar_url":"https://avatars.githubusercontent.com/u/1443215?v=3","gravatar_id":"","url":"https://api.github.com/users/DaSourcerer","html_url":"https://github.com/DaSourcerer","followers_url":"https://api.github.com/users/DaSourcerer/followers","following_url":"https://api.github.com/users/DaSourcerer/following{/other_user}","gists_url":"https://api.github.com/users/DaSourcerer/gists{/gist_id}","starred_url":"https://api.github.com/users/DaSourcerer/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/DaSourcerer/subscriptions","organizations_url":"https://api.github.com/users/DaSourcerer/orgs","repos_url":"https://api.github.com/users/DaSourcerer/repos","events_url":"https://api.github.com/users/DaSourcerer/events{/privacy}","received_events_url":"https://api.github.com/users/DaSourcerer/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/hosannahighertech/yii2-yiiboard/labels/task","name":"task","color":"bfe5bf"}],"state":"open","locked":false,"assignee":{"login":"DaSourcerer","id":1443215,"avatar_url":"https://avatars.githubusercontent.com/u/1443215?v=3","gravatar_id":"","url":"https://api.github.com/users/DaSourcerer","html_url":"https://github.com/DaSourcerer","followers_url":"https://api.github.com/users/DaSourcerer/followers","following_url":"https://api.github.com/users/DaSourcerer/following{/other_user}","gists_url":"https://api.github.com/users/DaSourcerer/gists{/gist_id}","starred_url":"https://api.github.com/users/DaSourcerer/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/DaSourcerer/subscriptions","organizations_url":"https://api.github.com/users/DaSourcerer/orgs","repos_url":"https://api.github.com/users/DaSourcerer/repos","events_url":"https://api.github.com/users/DaSourcerer/events{/privacy}","received_events_url":"https://api.github.com/users/DaSourcerer/received_events","type":"User","site_admin":false},"milestone":{"url":"https://api.github.com/repos/hosannahighertech/yii2-yiiboard/milestones/1","html_url":"https://github.com/hosannahighertech/yii2-yiiboard/milestones/minimum-viable-product","labels_url":"https://api.github.com/repos/hosannahighertech/yii2-yiiboard/milestones/1/labels","id":1091479,"number":1,"title":"minimum-viable-product","description":"Things needing completion to get a usable piece of software together","creator":{"login":"DaSourcerer","id":1443215,"avatar_url":"https://avatars.githubusercontent.com/u/1443215?v=3","gravatar_id":"","url":"https://api.github.com/users/DaSourcerer","html_url":"https://github.com/DaSourcerer","followers_url":"https://api.github.com/users/DaSourcerer/followers","following_url":"https://api.github.com/users/DaSourcerer/following{/other_user}","gists_url":"https://api.github.com/users/DaSourcerer/gists{/gist_id}","starred_url":"https://api.github.com/users/DaSourcerer/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/DaSourcerer/subscriptions","organizations_url":"https://api.github.com/users/DaSourcerer/orgs","repos_url":"https://api.github.com/users/DaSourcerer/repos","events_url":"https://api.github.com/users/DaSourcerer/events{/privacy}","received_events_url":"https://api.github.com/users/DaSourcerer/received_events","type":"User","site_admin":false},"open_issues":7,"closed_issues":2,"state":"open","created_at":"2015-04-30T17:56:46Z","updated_at":"2015-05-28T19:09:35Z","due_on":null,"closed_at":null},"comments":4,"created_at":"2015-04-30T19:05:36Z","updated_at":"2015-06-23T18:52:25Z","closed_at":null,"body":"Our database schema leaves a lot to be desired:\r\n* [ ] 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.\r\n\r\n* [ ] 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:\r\n * [ ] 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`;\r\n * [ ] 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;\r\n * [ ] renaming things for a more expressive and consistent nomenclatura. Examples:\r\n  * forums → boards\r\n  * topics → threads\r\n  * choice → poll_option\r\n  * logtopic → user_has_read\r\n\r\n * [ ] 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;\r\n\r\n* [ ] 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;\r\n\r\n* [ ] 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;\r\n\r\n* [ ] 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."},"comment":{"url":"https://api.github.com/repos/hosannahighertech/yii2-yiiboard/issues/comments/114606589","html_url":"https://github.com/hosannahighertech/yii2-yiiboard/issues/8#issuecomment-114606589","issue_url":"https://api.github.com/repos/hosannahighertech/yii2-yiiboard/issues/8","id":114606589,"user":{"login":"mtangoo","id":1502872,"avatar_url":"https://avatars.githubusercontent.com/u/1502872?v=3","gravatar_id":"","url":"https://api.github.com/users/mtangoo","html_url":"https://github.com/mtangoo","followers_url":"https://api.github.com/users/mtangoo/followers","following_url":"https://api.github.com/users/mtangoo/following{/other_user}","gists_url":"https://api.github.com/users/mtangoo/gists{/gist_id}","starred_url":"https://api.github.com/users/mtangoo/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mtangoo/subscriptions","organizations_url":"https://api.github.com/users/mtangoo/orgs","repos_url":"https://api.github.com/users/mtangoo/repos","events_url":"https://api.github.com/users/mtangoo/events{/privacy}","received_events_url":"https://api.github.com/users/mtangoo/received_events","type":"User","site_admin":false},"created_at":"2015-06-23T18:52:25Z","updated_at":"2015-06-23T18:52:25Z","body":"I will take on this this week"}}