Best way to link tables?

[krammer1]

I'm looking for a way to make this databse. I can already kind of make it I'm just trying to get an idea on the "best-practices"/most sensible approach.

 

You own houses and rent them out. You collect rent every month and you want to keep track of who owes you and how much.

 

So off the bat I'm thinking of these tables:

Houses table with columns like: the address , the price of the rent, the current tenant?

 

Tenants table with columns like: name, phone number, outstanding rent owed, the current address?

 

Payments table, a table of all the rent you collected, with columns like:

amount, date (auto via CURDATE), payed by, payed for property?

 

You would input the money you collect into the database from a php website. You would also use that website to send queries so you can figure out who owes you what. I don't need any help with php, just the general structure of these tables.

 

So what I'm not sure about is the columns I marked with a bold question mark. How should I link those columns between the three tables? Should I use a foreign key thing? Should I just use primary keys and make fancy queries?

 

Let's say I want it to show me a list of properties, how do I also make it show the tenant and the payments associated with that property?

[requinix]

You should use foreign keys whenever they're warranted. There isn't really a situation where you could use one but should not.

 

Here's what my layout would look like, though I tend to overthink things and it may be more complicated than you need:

houses:
- id
- address
- rent per month
- tenant (optional, FK to tenants, helps with queries)
- notes (always useful)

tenants:
- id
- name
- phone number
- date first moved in
- date moved out (nullable)
- forwarding address (nullable)
- current lease (optional, FK to leases, helps with queries)
- notes (always useful)

leases:
- id
- tenant (FK to tenants)
- house (FK to houses)
- start date
- end date
- price
- amount paid (optional, helps with queries)
- pay due date
- notes (always useful)

payments:
- lease (FK to leases)
- date
- amount
- notes (always useful)

[krammer1]

Ah I was thinking of a leases table but wasn't sure how it would fit.

 

I was also confused about FK because of all the controversy (even apps like wordpress don't use them?). I'm new to mysql and was surprised to learn that without FK there's nothing "relational" about this "relational database", other than tricking yourself into thinking it's relational with the right queries. I'm relieved that I can just use FK and not worry about it!

 

Perfect reply requinix, thanks so much!

[requinix]

Don't use WordPress as an example. Please. Their codebase features poor design and worse implementation.

 

Foreign keys (in terms of design) are simply fields in one table that correspond to fields in another table. Constraints aren't strictly necessary so long as the application is coded perfectly; since that can be hard there are foreign keys (in terms of implementation) that enforce the rule that referenced fields actually do exist before you try to use them. Note that they can do more, like automatically update or delete rows.

 

MySQL doesn't automatically relate things for you, if you're thinking along those lines. There isn't a "grab stuff in X table and all the 'related' stuff in Y table" function. You have to tell it exactly what you mean. Instead it gives you some tools for that, such as JOINs and foreign key constraints.