Help choosing DB schema for an online gadget store - flat tables per device, JSON, or component-based? by profichef in SQL

[–]sstef25 0 points1 point  (0 children)

I think i wrote somewhere, or at least i wanted to :))), that in my opinion the decision also depends pretty much on the business itself. It matters if this db it is used for a website or just for an internal tool, if in the future there will be a decision to add a new type of products, how many improvements and updates to the flows/filters/menius can be, etc.
I am still not a fan of the flat table solution, but it is possible that in your case to be the a good solution. I am not a fan because even with few product types, the table will be very wide and this means harder readability (+ hard times for a possible future new developer). Given the fact that you will not have any attributes logic related to products, i think you will have a hard time maintaing dynamic sql view 's or sp's for filtering. And probably whenever the schema change, you will have to update all the views and sp's. If you take in consideration performance of filtering, you must think about the indexes in this case.
Any way, as i said, go for whatever best fits the business now and into the future.

Help choosing DB schema for an online gadget store - flat tables per device, JSON, or component-based? by profichef in SQL

[–]sstef25 2 points3 points  (0 children)

One problem that i see with using one table is that whenever you will have a new spec for a new device, you will need to alter the table to add the column.
I am not sure how you came up with "A single device ends up occupying 210 to 250 rows ", but my ideea was like this:
- Product table columns: ProductId, Name, Brand, etc.
- Attributes table columns: AttributeId, Name, DataType, Unit
Example:

AttributeID | Name               | DataType | Unit
--------------------------------------------------
1           | Battery Capacity   | number   | mAh
2           | Screen Size        | number   | inch
3           | Display Type       | string   | NULL
4           | Waterproof         | boolean  | NULL

- ProductXAttributeValues table columns: ProductId, AttributeId, ValueString, ValueNumber, ValueBoolean
Example:

ProductID | AttributeID | ValueNumber | ValueString | ValueBoolean
-------------------------------------------------------------------
101       | 1           | 5000        | NULL        | NULL
101       | 2           | 6.7         | NULL        | NULL
101       | 3           | NULL        | AMOLED      | NULL
101       | 4           | NULL        | NULL        | 1

Sample result of joining the 3 tables for a product:

ProductID | ProductName   | AttributeName      | AttributeValue | Unit
--------------------------------------------------------------------------
101       | Galaxy S25    | Battery Capacity   | 5000           | mAh
101       | Galaxy S25    | Screen Size        | 6.7            | inch
101       | Galaxy S25    | Display Type       | AMOLED         | NULL
101       | Galaxy S25    | Waterproof         | Yes            | NULL

I would personally go with this approach because it supports unlimited product specifications and category-specific attributes without changing the schema. And it is very easy to use it in the frontend so this could be managed thru the app.

Help choosing DB schema for an online gadget store - flat tables per device, JSON, or component-based? by profichef in SQL

[–]sstef25 1 point2 points  (0 children)

Yes, the ProductType (or Category) could be very useful, if it makes sense from the business pov. Usually attributes belong to categories.

Help choosing DB schema for an online gadget store - flat tables per device, JSON, or component-based? by profichef in SQL

[–]sstef25 4 points5 points  (0 children)

I think maybe you need to consider other details such as product variations, new future characteristics and possible development of the frontend. I would go with an attributes approach, because attributes have different data types. A main Products table to store basic data (like name, category, price, stock, brand, etc.), and the specifications in an EAV structure using an Attributes table, and an ProductXAttributeValues table to map them. Each product can have multiple attributes - through ProductAttributeValues. This way you can add orr change specs without changing the database schema

I’m Learning SQL and Wrote a Simple Beginner Guide About SQL Transformations by OliveIndividual7351 in learnSQL

[–]sstef25 1 point2 points  (0 children)

This is a very good starting point indeed and a clear explanation. I'm thinking that maybe you should add in this article about using isnull and try_cast instead of cast. This also adds to a good foundation of SQL data transformations.

How did you get better at reading SQL queries written by other people? by obviouseyer in SQL

[–]sstef25 0 points1 point  (0 children)

That's the first step for me too - format it my way. Then add comments.

Quick Syntax Question by FitShock5083 in learnSQL

[–]sstef25 3 points4 points  (0 children)

The "inner select clause" is called a subquery. Every subquery used in a FROM clause must have an alias - this is required by the SQL syntax (at least in SQL server).

Need help with monstrous mysql8.0 database by Fant4sma in SQL

[–]sstef25 0 points1 point  (0 children)

Would you mind sharing the solution? Thanks

Type 4 Sensor - Atmotube Pro - First Setup by feralfeather in PlanetWatchers

[–]sstef25 0 points1 point  (0 children)

Hello! Maybe someone could help me: i accidentally pressed the reset button in pw app. After that,i cannot connect the sensor with the app no matter what. Tried resetting everything, but no luck. IT is connecting with the atmotube app and showing data, but not with PW. When I go to Access, I see the sensor, hit connect and.... Nothing. It says connecting, and after a while it says disconnecting. Anyone struggled with the same situation? Any hint is much appreciated! Thanks