Help with designing my mysql tables before coding in PHP

[bambinou1980]

Hello,

 

I am going to code my first php app but I have a design question.

 

My app will have 4 tables:

Customers

Products

Order

Resellers

 

 

 

Products will be first entered via a crud, then when the admin will pass an order he will need to populate the order form with the customer names via dropdown menu and also products names via dropdown menu.

 

The admin of the app will want to see the list of all the current orders. My questions are:

 

Shall i create a relation between the tables order, products and customers?

 

For me I would design it as separated tables because if a product is removed, I still want my order to retain the old product names in the invoice. Am I correct to do it that way?

 

 

Also I have another problem, products of the same name may have different prices according to the customer type.

How shall I build this "product" table?

 

I was thinking about making a "product" table and a "price" table, then have the product_id foreign key in the price table, would this be ok?

 

 

I just a bit of help to start with as I am worried to start coding the whole database in the wrong way.

 

Thanks again!

 

Ben

 

 

[mac_gyver]

if a product is removed,

 

 

in real life applications, data is not actually deleted. if you insert a row into a table and assign it an id (identifier), that row is never deleted, so that any data that uses that id will always be valid. if you want to make a piece of data unavailable at some point, you would have a status column that controls if it can be chosen.

 

I was thinking about making a "product" table and a "price" table, then have the product_id foreign key in the price table, would this be ok?

 

 

yes, you would have a price table that contains the product_id, customer type, and since the price for anything can change over time, a start date and end data that the stored price is applicable.

[Barand]

 

Shall i create a relation between the tables order, products and customers?

Yes, you are using a relational database.

 

 

For me I would design it as separated tables because if a product is removed, I still want my order to retain the old product names in the invoice.

Do not delete products. Instead add a "deleted" flag so they are removed from sale but descriptions are still available for historical data.

 

 

I was thinking about making a "product" table and a "price" table, then have the product_id foreign key in the price table, would this be ok?

Not only OK but essential, as the price is also dependent on customer type.

 

Reseller - wouln't that just be another type of customer?

 

From your brief description, I have identified the following tables (model attached)

Edited July 29, 2015 by Barand

[bambinou1980]

Wow thank you so much to both of you:-) great map Baran.

 

From product to "order item" is a "1 to many relationship"

From Product to Price is "1 to 1 relationship"

but from Order to invoice, I do not know

 

I do not recognize the link between in the order and invoice in your map, there is like a little circle, what does this one means please?

 

 

I see so having a new "delete" column would let me create an update query that sets it to 1 or zero then in the list table I can show the non delete first, neat.

 

One thing I am not sure about.....I had the problem years ago when I paid a dev to create an invoicing system for me, when customer detaisl changed(vat number, company name for example), this was also changing on the pre generated invoices(in the archives), so really when an order is done, you do not want too much relationships between the tables because the content that is being sent in the form has to be static and cannot eb modified dynamically, am I correct?

 

 

 

One last thing I wanted to ask you, what is the best way to query 2 tables that have a relationship:

ie:

$query = "SELECT id, cust_name, address, FROM customers WHERE id = $customer";

 

Let's say I wanted to also have (based on your map)the cust_type showing in my tables.

 

Thanks,

 

Ben

[Barand]

 

From Product to Price is "1 to 1 relationship"

Product to Price is 1 - Many.

Your own requirements stated different prices for a product depending on customer type. There will also be different prices depending on date (old prices and current price (and maybe future dates for planned price increases) This enables you always to get the price that was in effect on a particular date. So if the price changed last week, orders/invoices from last month will reflect the price that applied then.

 

 

but from Order to invoice, I do not know

Order to Invoice will 1 - 1 or Many. Normally you will raise an invoice for an order but on occasion a partial invoice may be raised then a second on completion of the remainder of the order.

 

 

so really when an order is done, you do not want too much relationships between the tables because the content that is being sent in the form has to be static and cannot eb modified dynamically, am I correct?

Again, overcome by applying from/to dates to changeable values like prices, tax rates etc.

 

The query

SELECT customer_id
, cust_name
, address
, type_descrip
FROM customers
    INNER JOIN customer_type USING (cust_type)
WHERE customer_id = $customer"

[bambinou1980]

Thanks Barand, I can see that you are very experienced in PHP, for how many years have you been coding please?

 

Do you believe in frameworks like Yii2 or Laravel or is it a waste of time?

 

Thank you,

 

Ben

[Barand]

My profile : http://forums.phpfreaks.com/user/3105-barand/

 

Alternatively, you may want to model it like this, allowing multiple customer orders on one invoice or multiple invoices for an order.

[bambinou1980]

Hi Barand,

 

Thank you so much, I am very impressed about your profile, wow, you have actually lived in coding/systems pretty much all your adult life, it must become totally natural for you to use those languages and logic...nice :-)

 

Thanks again for the map:-)

 

Ben