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!

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?