Could it ever be possible for a tenant have more than one lease with you?
Aren't leases specific to a unit or group of units rather than a property?
Could there be more than one owner for the same property?
Am I correct that a property can have more than one complex just a a complex can consist of more than one units?
Tenants can have more than one lease. Leases are specific to units. I have some units that are houses, so in that case the "property" doesn't have any "units". We also have some commercial properties that are single entities, so there would be no "units" in these either. The we also have some duplex's that are addressed individually. So they don't have a unit number. There could be more than one owner for the same property, but in our case there is not. That would be an nice ability to have for the future though. I never thought about a property having more than one complex, but that is a possibility. We currently do not have anything like that, but I could see where it could happen. One property could have several complex's. Thank you for your help.
Tenants can have more than one lease.
Then you need to link your payments table to an Tutorial: What are Table Occurrences? of Leases so that you can apply a given payment towards a specific lease. This might need to be more complex than that. If you accept single payments that pay towards more than one lease, you'll need a join table between payments and that occurrence of Leases so that you can apply a portion of the total payment towards each lease.
Leases are specific to units.
Then Leases should link to a table of Units.
There could be more than one owner for the same property, but in our case there is not.
I take that to mean that you do not currently have multiple owners for the same property but that you might in the future. If so, before you can fully manage that situation should it occur, you need a join table between owners and properties so that you can both have one owner with several properties and yet also have one property with several owners.
It would appear that you need these tables related in this one to many hierarchy: Properties----<Complexes-----<Units. Single home properties and single unit commercial properties would have one record in each of the three tables. An apartment complex with a single complex on the property would have one record in Properties, one in complexes but many records in units.
You might not need the Complexes table at all. It's main purpose would be to allow you to track expenses or income for different complexes on the same property. If you don't see any value for that, you could probably do without that specific table.
That helps a lot! Thank you so much for your help. I will put a new one together and post a screen shot if you don't mind taking a look at it.
Here is the new sample I've created. Does that look right? I opted to leave the "Complexes" field in so I could track expense per complex if we ever have a property with multiple complexes on it. As far as the fields in each table, some are logical and obvious and others I'm not sure which table to put them in. Does "rent" and "deposits" go in leases, units, or tenants? What would a good way be to handle a husband and wife? They would both be the leasing tenants. I also need to figure out a way to handle the status of units (old tenant out and needs to be cleaned, in process of cleaning, ready for new tenant). Could you suggest a good way to handle upcoming tenants? I would also like to have a way to track projects. These would include things like installing sprinklers in the yards, converting flat roofs to sloped, a complete remodel of a unit or the exterior of a unit/complex, ect... Based on what little I know, I would think this would require a separate table all together. Something else I thought about is in some of our complexes we have laundry rooms with coin operated machines in them. Initially I thought they would be in the units table but they will not collect any rent. I need to track their income and expense such as repairs and utilities. As always, thank you for your input.
Does "rent" and "deposits" go in leases, units, or tenants?
A rental amount is specified in leases. A rental payment is recorded in payments and linked to the correct lease.
The deposit amount is specified as a field in Leases. The receipt and refund of a deposit would be entries in payments and linked to leases.
What would a good way be to handle a husband and wife?
If you need to record contact info for each so that they are joint tenants, I'd probably use two records in Tenants and then add a join table between tenants and leases. If you just to need to list a spouse's name, you might be able to get away with an added field or two in tenants, but given all the possible living arrangments, multiple tenants records probably will work better.
I also need to figure out a way to handle the status of units
To just record the current status, use fields in the units table. If you need to track a history of status changes, repairs etc, use a related table if you need to keep that info.
I would also like to have a way to track projects.
I agree, and suggest that you include an join table link each project record to the units to which it applies with short cut buttons/scripts that enable you to select a property or complex (which then generates the links to units for you) instead of selecting units one at a time in the join table.
Laundry rooms are an interesting problem. I might choose to set them up as a unit with a 0 dollar amount lease. (You may have 0 dollar leases anyway for apartment managers that live on site anyway...)
But you could also set up a separate table for them and link them to the appropriate complexes.
Now to keep one more thing in mind: NONE of what we have discussed is chiseled in stone. Often, the data model for a given database undergoes periodic revisions throughout the database development process. So don't hesitate to go back and review and revise your basic design throughout the process of getting it up and working and especially do this after you have put your database into use for a short time--often that initial period of use will reveal the need for interface changes and these changes could result in changes in your data model as well.
Here is what I added. I wasn't sure about the "join" table for Projects. I'm thinking since you mention "join table" I've missed something on the projects table. You also mention that often the data model will change or evolve throughout the design. Just so I'm clear on this, I assume it means I can change things around and my data stays as long as I don't delete tables or fields? Is it safe to assume that I can add more tables as the need arises in the future? Thanks for the help.
Well I suggested linking a project to units rather than properties. The idea was that a given project might affect just part of a given property so if you used a join table to list all the affected units, you had a greater possible degree of precision possible. It's up to you to decide if you need that degree of details or not. The downside is that for projects that affect an entire property or complex, you have to create join table records all the relevant units for that property or complex--which is why I also mentioned "short cut" buttons/scripts that allow you to select a property or complex and then the script generates the needed join table records.
You'd need a join table here as a given project could affect many units and a given unit can be linked to more than one project over the passage of time.
But if you link projects only to properties, a join table is not likely to be needed so it's up to you to decide which better fits the needs of your business procedures.
I assume it means I can change things around and my data stays as long as I don't delete tables or fields?
Correct. But such changes can have profound affect on the look and function of layouts, scripts, etc so you still need to think things over before making a change.
Is it safe to assume that I can add more tables as the need arises in the future?
Yes. You can add more tables, more table occurrences and more relationships as needed in the future.
But one word of caution: FileMaker automatically saves all design changes that you make to your file. It's not impossible to be hit with what I call "developer's remorse". That happens when you make a complex change to your database design, test it and then decide that the design change isn't such a great idea and you need to revert to the previous design, only to find that you can't because all of your changes were automatically saved. FileMaker 13 offers more undo capabilities than before, but it's still a good idea to make and keep frequent back ups during the development process. That way you can save some effort by discarding the current copy and reverting to the previous copy if a recent change turns out to be a bad idea.
Since I usually get so intensely focused on my file design that I forget to stop and save copies of my file, I've developed a set of scripts that save the copies for me so that I don't have to remember to do so: Saving Sequential Back Ups During Development
Am I correct in using table occurrences for the Projects relationships or did I go overboard?
That will depend on the needs of your business.
Can a project affect more than one property? If yes, then a join between properties and Projects might be useful. The same question and answer works for projects to complexes.
In theory, you don't need anything but Projects----<Join>-----Units since linking a project to all the units of a given complex is functionally the same as linking it to a complex and linking a project to all the units of a given property is the same as linking it to that property, but I suspect that you will find relationships (but maybe not with the join tables) to properties and (perhaps) complexes useful ways to simplify scripts and calculations.
This is a good example of an area where you need not invest a lot of thought and effort up front. You can add such relationships or not as you develop the database and you gain a better understanding of what might help keep your overall system as efficient and simple as possible.
I doubt we would ever have a "project" affect more than one "property". I suppose it would be possible for a "project" to affect more than one "complex" if we had multiple complexes on one property, such as a irrigation system or something of that nature. If I just have the Projects----<Join>-----Units and had a project that was, for example, a new roof on the complex, could I somehow see it as a complex project or would it just be every unit in that project would show that project? I'm probably overthinking it and making it more complicated than I need too. Not knowing exactly how the scripting and shortcuts work I'm sure is affecting my thought process. As always, thanks for the guidance!
Once you have:
you can add more occurrences to get:
So for a given project, you can see a list of the units, Complexes and Properties affected by that Project
Ok, like this?
Another question, how can I duplicate a group of fields in form view? Example, I need to enter multiple sets of emergency info (name, p#) and of course have the name/number stay attached to each other.