general structure of db, will need to tweak more

[BrandonDenton]

I have laid out the general structure of the database, but I will need to tweak a couple elements and freshen up on Knex over the next day.

[le3bl]

Here is more or less what I was envisioning:

articles id - int - incrementing - unsigned - primary key (indexed) uri - string - indexed (for search capabilities) createdDate - datetime modifiedDate - datetime userId - int - unsigned - foreign key -> Users.Id - indexed isDeleted - bool

users id - int - incrementing - unsigned - primary key (indexed) uri - string authType - string/int (not sure how we will be doing SSO) isActive - bool createdDate - datetime modifiedDate - datetime

userArticles id - int - incrementing - unsigned - primary key (indexed) articleId - int - unsigned - foreign key -> Articles.Id - indexed userId - int - unsigned - foreign key -> Users.Id - indexed

roles id - int - incrementing - unsigned - primary key (indexed) name/uri - string createdDate - datetime modifiedDate - datetime isDefault - bool

userRoles id - int - incrementing - unsigned - primary key (indexed) userId - int - unsigned - foreign key -> Users.Id - indexed roleId - int - unsigned - foreign key -> Roles.Id - indexed isActive - bool createdDate - datetime modifiedDate - datetime

permissions id - int - incrementing - unsigned - primary key (indexed) name/uri - string createdDate - datetime modifiedDate - datetime

rolePermissions id - int - incrementing - unsigned - primary key (indexed) roleId - int - unsigned - foreign key -> Roles.Id - indexed) permissionId - int - unsigned - foreign key -> Permissions.Id -indexed

userArticleVotes id - int - incrementing - unsigned - primary key (indexed) userId - int - unsigned - foreign key -> Users.Id - indexed articleId - int - unsigned - foreign key -> Article.Id - indexed isUpVote - bool (I think this is how reddit does it) createdDate - datetime modifiedDate - datetime

tags id - int incrementing - unsigned - primary key (indexed) title - string - indexed (for search capabilities) createdDate userId - int - unsigned - foreign key -> Users.Id - indexed

articleTags id - int - incrementing - unsigned - primary key (indexed) tagId - int - unsigned - foreign key -> Tags.Id - indexed articleId - int - unsigned - foreign key -> Articles.Id - indexed userId - int - unsigned - foreign key -> Users.Id - indexed createdDate - datetime

[KixPanganiban]

This looks good @BrandonDenton, unfortunately we've moved to Laravel. See #10. You can re-do the models in Laravel, and we can merge that upstream instead.

[BrandonDenton]

Understood @KixPanganiban. I'm seeing a lot more documentation on Laravel's SQL functions anyway. I'm converting what I have and will submit a pull request in a few minutes.

[BrandonDenton]

new pull request made with more appropriate script, closing this one