Optimal DB Schema

Question

I'm about to redesign a DB schema and I'm considering using an ORM in my app, would the below schema work with, for example, Eloquent ORM, or would I also have to add the JOIN tables as well?

ISSUES(ID, ORGANIZATION_ID, DATE, TIME, CATEGORY_ID, TYPE_ID, ISSUE_DETAILS_ID)
ISSUE_DETAILS(ID, NAME, STATUS, EMAIL)
ORGANIZATIONS(ID, NAME, ADDRESS, CONTACT)
CATEGORIES(ID, CATEGORY)
TYPES(ID, TYPE, CATEGORY_ID)

Answer 1

The only thing I'd probably do differently is have the issue_id foreign key on the issue_details table so that you could have a one to one relationship.

So if you were using Eloquent you could do something like this.

echo $issue->details->name;

I'm not entirely sure what you'll be storing in the details table though, perhaps there will be multiple details for an issue in which case you'd have a many to many relationship.

Answer 2

In addition to what Jason suggested, you could also remove the category_id from the issues table since each type already has a category, you already have access to the category through the type.

i.e. $issue->type->category