How to add a product to the cart?

Question

MySQL

Hello to all. Tell me how to create a table for the basket and checkout of the finished order?
I have created many tables for different products:
For example, Coolers - prntscr.com/77eoqd
Video cards - prntscr.com/77eoy2
As you can see, all tables have different field names.
When I click on "coolers" - Buy, the cooler goes to the cart, and there I can increase the quantity of the product. And so it is with many products. When I click on "place an order", the data of these goods (name, quantity, price) are entered into the table.
And then you need to display information about the ordered goods - such as a report. product name, price, quantity. And so for each product.
I don't know what specific tables to create, what fields for the basket, whether to link? After all, I have more than one table "Products", where there are 5 static fields and only the name changes.

Answer 1

You have already been advised to use json, I will try to give a more general recommendation. You have a problem that is standard for your subject area - the lack of a single data schema that you have. It is very rare in a running application that someone changes the schema "on the fly" - i.e. in 99% of cases, the circuit (at least logical) is designed along with the application. New version of the application - new scheme (if necessary, of course). Thus, your task dictates that you need to work with PART of your data without the use of a schema. Agree, it's not very flexible - to start a new table for each type of product, of which there can be a huge number. Most importantly, new products are coming in every day, and adding tables every day is crazy. And it is completely incomprehensible how to develop SQL queries for a selection,
Thus, it is better for you to store some of the data differently, namely the attributes of your products. There are two approaches: this is either semi-structured data (semistructured), i.e. XML or JSON or EAVmodel. The latter is a classic way out of the situation when using relational databases, however, since it, by definition, goes against the relational data structure - this is all essentially a big crutch. Today, the first option is increasingly recommended, because. firstly, many modern RDBMS support JSON or XML data types, even with validation and sampling capabilities (and if not, BLOB will always help), and secondly, you can use any document database for a product catalog, which, by the way, is even better suitable for the specifics of the load on the directory (a lot of reading and little writing, integrity is not particularly needed - sharding will be without any problems).
Total:
1) understand why not all data can not always fit into a fixed schema
2) choose a suitable solution for yourself from those listed, remembering that it is possible (and necessary!) To store some of the data in a normalized form, and some of it will not work.
3) search and selection on non-normalized data is considered as a separate task
PS, just the same, it is enough to put the product ID and its quantity in the basket, which will perfectly fit into the relational scheme. And for this, there should be only one table of goods.

Answer 2

I agree with Stanislav, the database is designed out of hand, learn to do it right and listen to what experienced comrades tell you - you will save more nerve cells.
Stubbornness is good, but when a more experienced person tells you it's not well designed, then you should listen and not get a bunch of bumps on simple things.
There are several Normal Forms for databases. Each of them removes some redundancy. The base must be at least 3NF, read about 1NF, 2NF, 3NF and 3.5NF (or Boyce-Codd Normal Form or BCNF). Sometimes denormalization is carried out, but for this you need to understand why and in what cases this is done.
Now about your table structure.
In your case, there is a certain product, there are categories (or groups, your video cards, coolers and God knows what else) of goods. Here are two tables for you - Product, Product Category. Well, the relationship between them, of course, is one to many, so that many products correspond to one category, but one product always corresponds to one category. If a product can be in several categories (many-to-many relationship), then this is done through an additional table. In total, we have the convenience of adding new product categories - we add a new category and, when adding a product, indicate which category it belongs to. some tags can also be organized in the same way.
Next, we need an order, we make an Order table with the fields who ordered, when they ordered, other notes and fields, but not what they ordered. The tabular part of the order should be in a separate table, where there is a link to the product (one table and not like you have now), quantity, other fields, and a link to the order to which this or that item belongs.
If you store the basket in the database, then it can be implemented in approximately the same way, only there, in who ordered, for example, the client's session identifier. Well, indicate the date and time of the decay of the values ​​in the basket and, over time, remove the rancid ones or accumulate statistics on which products are most often added to the basket.
Product properties, as they said, can be stored in json or xml, but this is not convenient for filters of any kind, and adding / editing property values ​​\u200b\u200bis somehow not convenient - load, deserialize, change / add, serialize and save. If they never change and there is no search for them, then I would save them as an object serialized in json or xml. Otherwise, too, through the tables associated with the product by communication. here you can wind up a lot, and groups of properties for each category and everything else.

Answer 3

cart table will contain id, quantity and product category