Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
We have a site that offers various services. For example buying and selling currency - gif card and voucher code
Now what structure do you suggest for the order table? Because each of these services has its own specific information that must be stored in the table. If we want to have only one table to store orders, the number of table fields may be more than 50.
An orders table, and an order_items table (which is a pivot between Order and Product models) would be typical for most applications. The orders table would have Order information (customer, payment, delivery etc.) and the order_items would have foreign keys for order_id and product_id along with quantity and price
so i must create table order with this structure:
Order
- id
- user_id
- pay_amount
- pay_status
- ...
And another table with this structure to store order information
Order_items
- order_id
- quantity
- price
- ...
but why pivote table?
A pivot table because it is a many-to-many table between your products and orders table - it should have a reference (product_id) to the Product being Ordered. Otherwise, that structure is okay.
But I do not think it is a many-to-many relationship because each product can have many orders, but each order is specific to one product. I think the relationship should be one to many
An Order has only one Product, ever? In that case, a product_id on the orders table would suffice.
Yes, because at the beginning I asked the question that the site offers only 3 types of services And the problem is that each of these services has its own information that if I want to create just one order table, I have to create a lot of fields in it. As I said, there may be more than 50 fields So I say have an order table And three other tables for services
table Orders
table CurrencyExchangeOrder
table voucherOrder
table gifcardOrder
in the orders the same information will be stored as such as : user_id , amount and ...
And in other tables the information of that order will be stored
Is this structure I have considered correct?
And if so, how do I create relationships between these tables?
Just because only three services are offered it does not necessarily follow that an Order is for only one service.
Why are CurrencyExchangeOrder, voucherOrder and gifcardOrder necessary; are these not the products?
TBH I am having difficulty understanding exactly what the business rules are. How does one order for a gifcard differ from another order for a gifcard; same for voucher or currency exchange?
Yes, there are site products, but there is no product table. The difference is that for example when ordering a foreign exchange service the following information must be saved
currency_id
amount
fee
walet_address
address_tag
txid
status
description
Or in ordering a voucher, only 3 fields are needed
voucher_code
status
description
The only difference is that each of these three services has different information that should be stored in the table, and the problem is that I do not think it is right to have only one order table to store the information of each of these services.
Make a products table; or better, several products tables; and associate them in a polymorphic many-to-many relationship with the Order model.
You will find no comfort in accommodating all of the different information in the orders table. Then depending on the type of the Product, you can render discrete view templates which are customised to suit that particular Product. You will have the ability to extend/reduce your product range without needing to append to your orders table; or locate several different conditionals in the View layer of your application
you could use a polymorphic relationship to a table of the right type for the order
@mdelshad Have an orders table, and an order_items table. Your order_items table should have a polymorphic relation pointing to whatever product that line item holds, be it currency, gift card, voucher code, or whatever else you want to sell in the future.
However, you should also copy at least the description of the product and the unit price at the time of purchase. The reason being, if I buy a gift card for $20 today, but then you update the price later to $15, then I should still see $20 in my account. If you don’t and just have a foreign key, and show the price directly from the related product then I’m going to look at my order, see the product is $15 but that I’ve been charged $20, and I’m going to be very angry with you. So when an order is place, record the description and price at that time.
@martinbean @snapey Can you give me a sample code for this structure?
@mdelshad I just described the structure? An orders table. An order_items table. The order_items table has a polymorphic relationship to the product that line item represents.
I would offer a kinda of different approach (inspired by my colleague). I use classic order table which has basic columns and additionally to them column called json and json_version (or however you want to call it) as well as helper column for order status.
This way you store all the details such as delivery address, items, company details in the json itself. Reason? Well people make mistakes and delete / change stuff, if you have got everything based on foreign id's and relationships it will mess up your orders retrospectively. Once users submits the order it must be constant not based on relationships.
Thanks to json_version your can create a parser service which would parse and return array with keys for example and if you want to add some more data to orders let's say in two years you just edit the json and switch the json parses service based on 'strategy patern' (fancy words here).
It is also great idea to create order_status table so that you can have full order status log on every step of order - created, fulfilled, canceled, paid etc.
@rudolfbruder Nope. JSON columns can get in the bin. If you’re resorting to a JSON column, it means you couldn’t be bothered to think about a schema and gave up, and you may as well just use MongoDB.
The reason I said to copy product information over was for the very reason you outlined. Even if the referenced product is renamed, changes prices, or is even deleted, the order will still carry the name, price, and other details at time of order.
Same thing with addresses. If a user updates an address, it should create a new row so as not to change the addresses retrospectively in orders.
@rudolfbruder I agree with @martinbean, json columns should only be used if there are no other options.
I recommend reading this post on database design.
@martinbean I understand, what if i need to store like 10 properties from product and 10 values from address. Is it good idea to create table with so many columns ?
Please or to participate in this conversation.