Forum Discussion
Calculation between three related tables
Hi nuuomkt ,
There are a lot of good books on data out there. One of my favorites is either of Matt Allington's books. In them he discusses the difference between Lookup or Fact tables, and Transaction or Data Tables. The Fact tables have "facts" about an entity, such as anything having to do specifically with your user, or your salesperson, or perhaps your contract. Your data tables usually link to these fact tables and include a date. Therefore, you should have a calendar table as well. The relationship between these tables is usually from the data table (many) to the fact tables (one.) You will have one user Bill Jones ID 6 who may have many transactions as a simple example.
Sorry this is not the easy answser that you were probably looking to find, but if you don't set up your data correctly, nothing flows correctly. Matt does online classes as well, and no I don't get paid this, but it was super userful to spend the time!
Nathaniel
Hi nuuomkt ,
There are a lot of good books on data out there. One of my favorites is either of Matt Allington's books. In them he discusses the difference between Lookup or Fact tables, and Transaction or Data Tables. The Fact tables have "facts" about an entity, such as anything having to do specifically with your user, or your salesperson, or perhaps your contract. Your data tables usually link to these fact tables and include a date. Therefore, you should have a calendar table as well. The relationship between these tables is usually from the data table (many) to the fact tables (one.) You will have one user Bill Jones ID 6 who may have many transactions as a simple example.
Sorry this is not the easy answser that you were probably looking to find, but if you don't set up your data correctly, nothing flows correctly. Matt does online classes as well, and no I don't get paid this, but it was super userful to spend the time!
Nathaniel
Hi Nathaniel,
Do you mean that the real issue here is that the data is wrongly structured? Like although I have three tables here, I should re-form them into one fact table and several data tables, and then link all the data table to the fact table?
I always just go with the original tables that I've got, and never think of this way...
Could you share some ideas on the difference between the fact table and data tables, or the requirement of each?