Jump to content

Help with designing my mysql tables before coding in PHP


bambinou1980

Recommended Posts

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

 

 

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

 

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)

post-3105-0-97009300-1438169424_thumb.png

Edited by Barand
  • Like 1
Link to comment
Share on other sites

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

Link to comment
Share on other sites

 

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"
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.