Basic database setup for a property ticket system

[Howan]

Hey Kevin and others,

I would like in the long run built a system for property management.
I tried to sketch the tables i would need and wanted to know if you think this is something logical.

There are houses with multiple owners for each flat.
Theoretical a owner can have different flats in different houses so i added houses_users
When users are logged in they can use a ticket system to communicate their problems.

Question here. I thought its logical to break this up in a base ticket table and the actual back and forth communication will be stored in the ticketsnippets.
Tickets and polls should always be visible house wide.

Is that a appropriate database setup?

I will not be able to program that right now but would be great to start the right direction and in a way CakePHP makes it easy.

[KevinPfeifer]

I am just going to let you know which table names are not correct according to our Naming Conventions

• supplier should be suppliers (table names are always plural)
• houses_id in houses_suppliers should be house_id since column names (aka fields) should always be singular
  • same goes for houses_users table and both its column names
• created and modified should always be datetime, not date
• users doesn’t need a house_id since its connected houses are saved via the pivot table
• no idea how you connect ticketsnippet and tickets but there should probably be a ticket_id in ticketsnippet
• articles_tags should probably be called tags_tickets (and article_id should be ticket_id)
  • The order of the tables is defined The names should be pluralized and sorted alphabetically

If you have that you should at least get a working app generated via

bin/cake bake all --everything

and can test if everything behaves as you expect it to.

I can’t tell you if this DB structure supports what you want to achieve but I’d definitely recommend sticking to the database naming conventions.