0

I'm building a personal real-estate site to track property expenses and calculate returns. Currently I have a single expenses table that tracks expenses for both properties and for individual units (a unit is related to a property). My expense table looks like this:

| expense_id | property_id | unit_id | amount | date |
| ---------- | ----------- | ------- | ------ | -----|

I have both property_id and unit_id set to allow nulls, there should only be values for either property or unit, but never both. IS this properly normalized or does it make sense to create two separate tables one for property_expenses and one for unit_expenses?

philipxy
  • 14,867
  • 6
  • 39
  • 83
user1347026
  • 588
  • 1
  • 8
  • 14
  • A "unit" is just a type of "property"; so I can't see why you have those as separate items. There probably isn't enough detail in this question to arrive at a definitive answer. *Not sure if this is a stackoverflow question.* – Paul Maxwell Apr 19 '18 at 02:49
  • There are properties with multiple units and I need a way to relate them to be able to roll up all the expenses associated with a property. You're probably right, this question may fall outside of stackoverflow's realm. – user1347026 Apr 19 '18 at 03:14
  • Hi. ["Normalization" doesn't really make sense when there are nulls.](https://stackoverflow.com/a/40733625/3404097) It doesn't mean "good design". This is a faq, sql/database subtyping/hierarchies/polymorphism & antipattern multiple/many FKs to multiple/many tables. Properties & units are types of things with expenses. Always google many clear, concise & specific versions/phrasings of your question/problem/goal & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using use one variant search for your title & keywords for your tags. – philipxy Apr 20 '18 at 21:21
  • Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Apr 20 '18 at 21:22

1 Answers1

0

To me this appears to be two separate questions. I'll answer each separately.

1) Is this properly normalized?

Attempting to represent both unit and property violates the first normal form which encourages creating a separate table for each set of related data. If you tweaked your representation a bit you conform to normalization rules.

2) Does it make sense to create two separate tables?

It depends on your use case and the format of your data. If you are wanting to model a one to many relationship in which a property can have multiple expenses you should have separate tables for properties and expenses using a foreign key to establish the relationship.

hackzor12
  • 1
  • 2
  • Thanks for the reply, Unfortunately I need a way to link the expenses to a specific unit or property, I'm not sure how I would do that in your second example as it just specifies if each expense is for a unit or a property but it does not distinguish which exact property/unit the expense is for. – user1347026 Apr 19 '18 at 03:17
  • You should have a table for expenses and a table for properties where the id of a property acts as the foreign key in the expense table. This would model the one to many relationship you are trying to achieve. – hackzor12 Apr 19 '18 at 03:58