Questions tagged [best-practices]
Best practices are generally and informally recognized as the methods and processes that have been shown over time to be superior to those achieved by other means.
386 questions
-1 votes
1 answer
93 views
What are the best practices of a banking relational database? [closed]
I don't have any particular problem in mind, I am just looking for the best practices. I am creating a bank database. Nothing too important or involving real money, nevertheless, I think it would be ...
- 1
2 votes
1 answer
883 views
Why is it considered best practice to partition columnstore tables?
Prior to SQL Server 2016, partitioning columnstore indexes was considered pretty much mandatory because the locks taken on them during inserts/updates/deletes were extreme. However, as of SQL Server ...
- 1,342
3 votes
2 answers
614 views
Why is running database integrity checks in parallel beneficial if you are following best practice on Enterprise Edition?
On Enterprise Edition and Development edition, database integrity checks (e.g. DBCC CHECKDB) can run in parallel. According to best practice, backups should be tested regularly. So if I'm following ...
- 1,342
-1 votes
1 answer
110 views
MySQL design and best design practices
I am designing my first database for a mobile application, which is also planned to be developed into a web application. The photo shows the part responsible for user authentication and information ...
- 1
0 votes
0 answers
137 views
Best practice for applying complex business logic during transformation in ETL from Aurora db to Redshift data warehouse
I'll be pushing data from an Amazon Aurora db to a Redshift data warehouse. The painful part is that the transformation portion of the ETL workflow relies heavily on business logic, that lives within ...
- 143
11 votes
6 answers
3k views
Is it bad practice to have a "most_recent" boolean column in addition to a "create_at" timestamp column to track the latest version of a record?
The table looks like this, it's SCD type 2: +-----------+------------------+------------------------+ | id (text) | version (serial) | created_at (timestamp) | +-----------+------------------+---------...
- 213
2 votes
2 answers
117 views
MongoDB schema for visit and like
MySQL serves as our primary database, while MongoDB is employed for handling intensive write operations. I have been tasked with implementing a system for tracking visits and likes on products. The ...
- 21
1 vote
1 answer
65 views
Modeling a database for business that require several roles and privileges
Part I I have a database model with members and organizations with each organization has many members, so it is clear that is a one-to-many relationship. The difficulty is that among those members ...
2 votes
3 answers
114 views
How to let you change the order of products?
On the database there is this table: products: id name type 1 product 1 1 2 product 2 2 3 product 3 2 4 product 4 1 5 product 5 1 6 product 6 1 7 product 7 2 the select to extract them is: select * ...
- 25
-3 votes
1 answer
63 views
Good training on shrinking / growing files on SQL Server
I am looking for basic file shrinking / growing training for SQL Server. I see a number of similar questions as I post this - will read those answers now
- 11
1 vote
1 answer
305 views
Best design pattern for Posts and Comments, whereas Comments are also Posts
I have a Posts table and want to be able to have comments that are full fledged Posts in their own right. So instead of creating a Comments table I was thinking of adding a parent_id column to the ...
- 113
2 votes
1 answer
2k views
PostgreSQL use upsert with ON CONFLICT, or with separate INSERT and UPDATE statements?
We have a PostgreSQL 14 database where we store products fetched from online platforms. We have scrapers that are run on schedule which fetch products, and this data is then either inserted if the ...
- 23
2 votes
2 answers
767 views
Is there a best practice for resolving dependencies of SQL objects for build during development?
I have a greenfield project involving a SQL database that is under development. There is no production data involved yet. There is a rather long list of specifications which I expect can be ...
- 449
-3 votes
1 answer
124 views
Best practice for person data
Say I am designing a data model for a company with multiple 'actors', at the least employees and customers, but perhaps many more. There's likely some common core for this like name, address, phone/...
- 95
2 votes
1 answer
296 views
SQL Server JOIN vs sparse table
I am in the need to query a relation between an entity called article (it's a product sold in a shop) and it's characterstics (a set of attributes). The result will always presented in a paged manner (...
- 368