No announcement yet.

Mysql Database Structure Advice

  • Filter
  • Time
  • Show
Clear All
new posts

  • Mysql Database Structure Advice

    Hi Everyone,

    I'm going to attempt to build a database for our family business but need advice on the best structure for tables etc.

    We will then add customers, jobs etc via a PHP built front end.

    Our business is a Signs and Print business and our customers needs can be varied and mostly it is bespoke but no matter if it's one off or repeat business it all breaks down into the same data needs of Quantity, Description and Price.

    So I know that within my Database I will start with a table for Customers:


    So now I need to start a new "job" for that customer every time they come to us, some will be regular some not, each time we treat it as a new "job". These jobs can have many different items we produce.

    So for example we've just had a customer come to us for whom we put a graphical livery onto his van, this would be considered 1 "item" in a "job"

    Conversely he then ordered some embroidered clothing from us, again this was a new "job" but this had 45 "items."

    Both of these jobs are broken down into the same 3 things per "item" Quantity, Description and Price.

    How would I best go about keeping it all into "jobs" would I create a new table per job? or would I be better to have every "item" for all customers in an "item" table but identify them with a Linked customerID?

    I'm really new to all this but have done the smallest amount of basic mysql databasing the past.

    Would really be grateful of some advice.

    My questions might not make any sense but at least they are interesting.

  • #2
    Hi tomjelfs,
    I would use a relational database structure for this.
    1 table for all your items like so -
    item_id | item_description | item_price
    another table for your customer orders (id column being an auto incremented integer) -
    id | customer_id | item_id | item_quantity | order_date
    you could then easily update your items table with new items whenever you needed to and your orders table can be joined to your items table via the item_id

    hope that helps, let me know if you need help with the queries for grabbing the data from multiple tables.


    • #3
      That's great, thanks for that, I guess I will start tinkering.

      My questions might not make any sense but at least they are interesting.