I want to create a database, data about each user, his products and, accordingly, a photo of these products, tell me in which direction I should move, because you can’t create one big basket because, one user can delete all entries. Sorry if I not good explained

...but OK. You're going to have one database, and this database will have several tables. First off, you;'re going to need a table of items. Theoretically, you're going to need another table of suppliers as you'll need to buy these items. You'll need a table of stock quantities (and history?) so that you know if you need to order 50 widgets or just 5. The users is another table, then as you've alluded to, another table to store their order history. This is now a GDPR nightmare, so you're going to have to think about encryption. Especially if users are going to be putting their own orders into the system. Photos of products sounds OK, but things like T-shirts, you can have different styles - men's, women, children's, etc. Agsinst each product you also need to know supplier quantities - minimum and maximum values, etc. Also (thinking about the trailer company I worked at) if you stock a YP-7732 and a YT-3321, together this was actually a YU-8896, so if a customer ordered 7 of those, you needed to know if you had 7 YP-7732 as well as 7 YT-3321. Cross-referencing is fun.

Still think this is a good beginner's project?

Thanks for the laughs, Chris.

josé

The ERP system I developed they asked, 'Can it help order stock items for all these orders queued up? Poor Beryl here has to spend hours every day going through working out how many widgets we have to order for every order.'

'Sure - should be simple' I replied. Sketched out a quick system and it started to fan into about thirty tables.

'So will this link into our stock system as well?'

'Sure - should be simple' I replied. Sketched a bit more and added about another twenty tables.

A sales platform is more than a 'users' table, 'orders' table and a 'history' table ;) Just trying to help SqliteBeginner and any preconceptions he may have...

If all you want is what you said, it's easy enough:

CREATE TABLE users(
  id INTEGER PRIMARY KEY
  user_name TEXT COLLATE NOCASE UNIQUE,
  first_name TEXT NOT NULL,
  last_name TEXT,

{ ...other user properties here... }

);

CREATE TABLE products(
  id INTEGER PRIMARY KEY,
  user_id INT REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE,
  product_code TEXT COLLATE NOCASE UNIQUE,
  product_class TEXT COLLATE NOCASE,
  brand TEXT COLLATE NOCASE,
  description TEXT,

{ ...other user properties here... }

  product_image BLOB
);


An example insert for users may be:

INSERT INTO users(id, user_name, first_name, last_name) VALUES
 (1, 'jimmy@mail.com',     'James', 'Jones')
,(2, 'jane@othermail.com', 'Janet', 'Jameson')
,(3, 'john@moremail.com',  'John',  'Jacobs' )
;

And if Janet and John each adds a product, for instance a shampoo and a fishing rod respectively:

INSERT INTO products(id, user_id, product_code, product_class, brand, description) VALUES
 (1, 2, 'SHA001', 'Shampoo', 'Jane''s Haircare', 'Honey & Almond Wash')
,(2, 3, 'ROD007', 'Tackle',  'Johnsons', '9Ft high-flex 40Lbs Rod')
;

Note that to add the image, you will have to add the bytes as a BLOB in the product_image field. This is somewhat more advanced and will need reading the BLOB handling documentation.

Note also that because of the referencing (Foreign Keys) if you delete John, the Fishing rod will automatically also be deleted, this may or may not be what you want. Read up on Foreign Keys to understand how they behave, and also note that in SQLite you can switch foreign-key checking (enforcing) on and off if you need to.

Lastly, if this is a fun project, then it's good to do and will be very educational. If you are seriously trying to make an ERP system, it's almost always better to use an existing one, these things are so widely deployed and almost any of them have decades of design and refinement behind them. There are even free/open source enterprise-level ones out there, my favourite being ERPNext which will do everything you want and much much more for free (if you host it yourself on a linux machine) and extendible using Python or any of the myriad of API's.

Good luck!

You don't know enough about the subject to even ask the right questions.

Hire a programmer. That's what they're for. Or learn to be one.

Use an existing platform where you can subscribe as a merchant, there are many. But don't build one or even hire someone to build one. At least not until you are successfully selling stuff.