CODEWITHSUNDEEP
mysqlsqldatabasedatabase-designdatabase-schema
user1113534
user1113534

Reputation:

Structuring up a database

I have some trouble setting upp a database. Not any coding problems but can't figure it out how to structure it.

The idea is that people will sign up on the site, and with SQL they will be in the table USER. But the users will also availbaile to have a collection of handmade products so other users will see and rate their handmade products and see a lot of others information. So instead of having a table for each user, is there any easier way?

The product should only be "tied" to one user. No user would be able to have same identical product.

Like how does facebook work? I dont think every user have their one table for the information on use? The products could be like a picture. So every album dosen't have a table? Right?

Upvotes: 0

Views: 69

Answers (2)

just lerning
just lerning

Reputation: 866

The usual SQL approach is to create a table for the users and a table for the products. The products table should have a row "user" which contains the user id (or something else identifying the user). You might want to assign an ID to each user (e.g. autoincrement int) and use it.

You can then select all products of a given user:

SELECT * FROM products WHERE user = 723;

Or you might want to find the name of a user who made a product:

SELECT name FROM users WHERE rowid = (SELECT user FROM products WHERE product_name = 'Kitchen Chair');

By the way, I'd always use lowercase names for tables and rows in SQL.

Upvotes: 1

makciook
makciook

Reputation: 1555

Having separate table for each user is intolerable and completely wrong according to relational database model!

The goal you want to achieve is actually quite a basic thing in DB structuring, try reading some tutorials in that topic.

With a table USER and PRODUCT and properly set key fields, you'll be able to get the right form.

USER: user_id, << user details >>

PRODUCT: product_id, user_id, rank, << other product details >>

So with a query below, you'll be able to get all products of your users.

SELECT * 
FROM db.user u 
(LEFT) JOIN db.product p
ON u.user_id = p.user_id

Upvotes: 1

Related Questions