Tables PHPMyAdmin MySQL

[Emil_Loenneberg SE]

I have these three tables in PHPMyAdmin MySQL.

I am trying to make a website where I can add a product to a cart.

 

 

Products - [Id, Name, Description, Price]

Carts/Lists [Id, TotalPrice]

ProductsInCarts [CartId, ProductId, Quantity, Price, TotalPrice]

 

But I have no idea how to relate them or how many characters they should allow, neither do I know what type or attribute should be added.

 

I was thinking that products are added to basket in ProductsInCarts where CartId relates to Id in Cart Table, ProductId to Id in Products etc. But I am not sure how to do it.

 

 

 

[bredy]

I would probably go with something like this for the products table:

CREATE TABLE `products` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `price` decimal(10,2) NOT NULL
)

With id as primary key.

Pretty straight forward i guess. The main difference between varchar and text ist, that you can set a limit for varchar (text is fixed at 65535 chars) and text can not be fully part of an index.

The length really depends on expected data, but can be changed later if needed.

 

[Emil_Loenneberg SE]

18 minutes ago, bredy said:

I would probably go with something like this for the products table:

CREATE TABLE `products` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `price` decimal(10,2) NOT NULL
)

With id as primary key.

Pretty straight forward i guess. The main difference between varchar and text ist, that you can set a limit for varchar (text is fixed at 65535 chars) and text can not be fully part of an index.

The length really depends on expected data, but can be changed later if needed.

 

Thanks so much, so should I not add anything to the other tables and just go like that?

[Eigenvektor]

10 hours ago, bredy said:

With id as primary key.

If you want id to be a primary key, I would define it as such:

CREATE TABLE `products` (
  `id` int(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `price` decimal(10,2) NOT NULL
)

A primary key is both unique and not null by default. It also get an index, which is important once you have a lot of entries and you do queries with JOIN in them (for performance reasons).

 

The "auto_increment" means you get a new primary key value on insert automatically, i.e. you can do

INSERT INTO products (
    name,
    description,
    price
) VALUES (
    'Some name',
    'Some description',
    5.2
)

And the system will automatically generate the next id for you, no need to query the highest id yourself before insert.

 

You probably also want an Inventory table, where you keep track of how many goods you have left in storage, so people can't put stuff into their cart you don't have.

[C2dan88]

I would just have one table for storing the cart. The cart table will have three columns user_id, product_id and quantity. Foreach product the user adds to their cart insert a new row into the cart table storing their user id, the product id and the quantity of the item they want.

 

To retrieve the price and total price of the items in the cart when you got to display the shopping cart your query may look like this

SELECT 
    p.id, p.name, p.description, p.price,   # select product info from the product table
    c.qauntity,                             # select the qauntity from the cart table
    p.price*c.quantity AS total_price       # we calculate the total price of the item
FROM cart AS c
JOIN product AS p                           # join product table
    ON p.product_id = c.product_id          # where the product ids match in both tables
    AND c.user_id = $your_user_id           # AND the user id mataches

$user_id being the logged in users id

 

[wasab]

Doing a EVA model is probably the industry standard. 

 

https://en.m.wikipedia.org/wiki/Entity–attribute–value_model#:~:text=Entity–attribute–value model (,given entity is relatively modest

 

There is also database normalization techniques when designing database. 

 

https://en.m.wikipedia.org/wiki/Database_normalization

 

 

[Skiiwee29]

@Emil_Loenneberg SE I have merged your 3 threads on this topic together. Please avoid posting the same or very very similar topics and stick to one.