Property Management App

[Cheryl]

Hello - creating a property management app for internal use only. When creating a table and we have a building that has multiple units - would I give the building a unique ID or each individual unit a unique ID. The intention for the app is to manage each unit, connecting tenants to that unit and creating a record of maintenance and more.  

[kpcollier]

@Cheryl I think you should give both of them a unique ID. 

I would have a units table and a building table. Make a 'Building ID' integer field in the Units table. In Relationships, connect the ID field of the Building table to the Integer field you created in the Unit table. Now each unit is assigned a building, and you don't have a big mess of either having a table with a mix of units and buildings and the rest of the fields nor a bunch of repeating 'unit' fields in your business table. This will make it easier to edit and update each individual unit. 

From there, you can make a View that joins the two tables on Business ID. You can customize these as such to limit each view to just one building if you'd like. Or maybe for some reason some of the units should be left out of the datapages when updating and editing, you would now be able to do this through Views.

This would also make it easier to assign users from a User Table to a specific unit in a specific building. 

 

[Cheryl]

@kpcollier thanks for the insight - it sounds like I shouldn't make the mega table with all of the information, rather piece out all of the information. Would you suggest that I create separate tables for things like Property Management companies and HOA's too

[kpcollier]

@Cheryl Yes, I would. It may seem like a lot of work, but it will make things so much easier on you once you are done. 

As for an example (sorry I don't really have anything property management related), I have an app for quoting pieces of glass and windows. For this app, I have a table for the different thicknesses of glass, a table for the different colors of glass, a table for the films that can go on glass, and a table for different patterns that we can cut into or shape the glass. These tables would represent the HOA, Property management, maintenance tables. Then I have a Quotes table (which would be like your Building table) that has a relationship with all of the other tables. 

This way it is much easier to edit the records. Say, what if one of the Property Management's changed their name? Then you would have to go through each record in the megatable and change it manually, or set up a trigger to affect all records which could be kinda scary. With the above workflow, you would just go to the Property Management table and change the name there once, which would reflect on all related records.

If you would like to learn a little more about why these are the best practices and examples of how it helps, I recommend reading this article or googling 'normalizing database tables'.

 

[Cheryl]

@kpcollieryou are so right - much smarter to do all the work on the front end. Thanks again for your quick response - I am so excited to be working within Caspio for this project.