title: Advice on how i should structure my database
body: <p>I've progressed well on my dashboard i'm creating for a plumbing business (My Own) we specialise solely in new homes, and i'm trying to automate a lot of the processes i currently have to do manually, mostly arbitrary paperwork and management of jobs. I can also give links to the builder so he can track the progress of the property and the history.</p>
  
  <p>The general gist of this is so i can create a new property, From there the house plan is added for the property where then all sorts of details, like length of drains, type of roof, distances between areas can be added to children tables. This info will be used to create a quote from also.</p>
  
  <p>I would like some advice on how i should structure all of this. I am using php and mysql.</p>
  
  <p>I get a monthly database of prices emailed to me from my supplier in CSV format, so i would want to update that table in my database monthly also. So that each new address can use that pricing to create the quote, once the quote is generated i can save that out to another table so the prices remain frozen for that period when the pricing was set.</p>
  
  <p>One main question i have in terms of doing the quotes is how would i link my data to the pricing table? As i can not guarantee that the list i get will always be in the exact same order, also if new products are added it would mess up any sort of id system i had in place.</p>
  
  <p>I was thinking maybe the only way was to have each item search the database for the exact product code, which seems the most logical. </p>
  
  <p><img src="https://i.stack.imgur.com/sZQ1N.png" alt="enter image description here"></p>
  
  <p><a href="https://i.stack.imgur.com/sZQ1N.png" rel="nofollow noreferrer">http://i.stack.imgur.com/sZQ1N.png</a></p>
  
  <p>I've attached an image trying to figure out how i should structure it in my head. This would also include a pricing list obviously and many other tables that would be children of the house plan.</p>
  
  <p>The property Table is going to be the main table, which will have a owner tied to the property. As this can change if sold etc. Then a property has a house plan added to it, as some properties can have multiple homes on it.</p>
  
  <p>The house plan table then also has certain stages attached to that plan, "internal drainage", "External drainage" etc which will all be able shown on the page as cards underneath the parent house plan.</p>
  
  <p>Anyways i may have confused you guys but any advice is greatly appreciated or even some directions to some good advice. I have searched but my search terms leave a lot to be desired so i'm not too sure exactly were to look.</p>
  
  <p>Thanks guys</p>