I'm new user and i need some help about the best way to build a database that saves all data...
Let me explain, my database will not have any Update, every time the user "updates" some data, a new field will be added to the table as a copy of the previous, with update values in the changed ones.
I was building this, in a master\slave kind of way, the master has the ID and the Name that identifies the row, and then the salve row have all the values of that row (with a PK/auto-increment), every time the user changes values, i create a new row in the slave table and make the necessary changes. Everything worked fine with this approach until now...
Now i need to associate other table to this one where the user can make changes in the same way (history), and that it's letting me stuck.
The database data it's for a building company (constructions), that design components, send them to approval, after approval send them to production, after production send them to expedition to the construction site (table with all the info, and for each of this steps i need to register at least the when and who, every one of this steps are made inside of the company). Each component it's associated with a construction (table with the number of the construction and some relevant data), and some components need some material (this is the new table), in this material orders it saves the order num, and some other info like state of the order.
1 Construction has X Components
1 Component has X Material