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.
384 questions
0
votes
0
answers
21
views
Best practice for distinguishing periods from timestamps in database tables
I am working with weather data. Historic data which was measured and then documented and on the other hand predicted data.
In both cases it is possible for the data provider to either hand over data ...
- 159
-1
votes
1
answer
81
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
77
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
83
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
38
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
92
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:
...
- 25
-3
votes
1
answer
50
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
271
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
1k
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
1
vote
1
answer
602
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 ...
- 439
-3
votes
1
answer
89
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
246
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
0
votes
1
answer
33
views
When performing a migration, how do you handle a new column needing a value but not being able to provide one for the migration?
I'm adding a timezone column to a table; the value cannot be NULL. However, I can only alter the table if I add a default value, but there isn't a way to determine that value before the migration.
How ...
- 1
2
votes
0
answers
25
views
Is this the best practice for separating all records by user company?
This is more of a general database question than specifically a Prisma question, but the implementation I have is written using Prisma connected to MySQL.
Take the following part of my schema.prisma:
...
- 131
4
votes
1
answer
77
views
best practice for dependency roots
When a database has a dependency table showing the parent-child relationship of items...
I've mostly seen guides that assume the top level parent has a "parent id" field which is either zero ...
- 41
1
vote
0
answers
657
views
database design - sales, products, customers, persons and organizations
I am trying to model a system where you can have persons or organizations as customers.
persons or organizations can also have other sub-types like employees, suppliers and other types in the future.
...
- 121
-1
votes
2
answers
51
views
Select and prepare data on backend or make the finetuning on frontend? Performance | Best Practise
I have a table with transactions in my DB, here is an example.
# id, user_id, type, eur_amount, usd_amount, convert_currency, convert_currency_amount, eur_exchange_rate, usd_exchange_rate, ...
- 103
3
votes
2
answers
784
views
Single-row Settings table: pros and cons of Joins vs scalar subqueries
I work with an application that uses a SQL Server database including a number of tables that hold a single row of configuration data, which is sometimes needed in queries against more conventional ...
- 151
0
votes
1
answer
148
views
Storing different properties in different tables or storing all properties in one table?
I have multiple properties that are joined with the items table
all of them can be stored as a string, should I have multiple tables for different properties like:
colors table, images table, etc.
or ...
- 1
0
votes
1
answer
41
views
One table with a type field or separate tables for limited number of types?
I have the services table and the products table, they have identical fields; price, description, etc.
Is it better to have one table with a type field that can either be product or service nothing ...
- 1
-1
votes
1
answer
62
views
NoSQL DB candidate for the project (looking for experienced advice)
Intro
I am stuck with the limitations of the DBs I've tested* and need someone experienced to give me some advice.
Project details
Data sets
The project has 2 sets of data, the sets of items and their ...
1
vote
0
answers
25
views
Should I create a record for every user or wait for user interaction [closed]
To summarise, I have a list of questions in table questions, a list of possible answers in table answers and a table called user_answers with the selected answers from the users.
There are endpoints ...
- 111
3
votes
1
answer
395
views
Lists and list of items instead of one table per 'list'?
A sql database I am working on at a customer has been created with an unusual structure.
For example, instead of having one table for each of the following:
Country,
State,
City,
Units (in, m, ...
- 143
0
votes
1
answer
692
views
Bigint vs varchar datatype. Should I match source system? [closed]
Unfortunately, I think this is an opinion question. But I'd like to know if there is a best practice.
Our datawarehouse uses a bigint as a primary key for an address table. The existing records all ...
- 113
5
votes
1
answer
4k
views
Naming conventions for views and materialized views: "*_v", "v_*", "*_mv", "mv_*" or none? [closed]
My view stores quality metrics for various gizmos1 in the database. Today I designed it to be a view. In a few years, as we produce more gizmos, it may need to be changed to a materialized view.
I was ...
- 105
1
vote
1
answer
982
views
How do I set up row level security based upon relationship in another table in PostgreSQL?
I'm working on a social media app with PostgreSQL where I need the following features:
Users must be able to search for other users based upon their display names (display names are public)
Users ...
- 11
2
votes
1
answer
1k
views
Adding data to a DB as migrations
Some Background
We currently have a PostgreSQL DB that is "read-only", i.e. no user is allowed to write to it and all insertions are done in a controlled manner.
For a while, these were done ...
- 121
0
votes
2
answers
85
views
Choice between two antipatterns: Overdesign versus Over-optimization
Sometimes I need to keep state, like an IsVerified flag.
There are two options:
boolean (or bit)
Advantage: smallest and fastest
...but in theory "over-optimization" and thus a bad argument.
...
- 385
0
votes
1
answer
794
views
SQL: Entity belongs (many-to-one) one of two different entities
Let's say I have organization and person and they have a one-to-many relationship with post.
post could only be owned by either an organization or user (person).
What I'm doing right now is making the ...
- 27
0
votes
1
answer
619
views
How bad is to prefix table names to order them exactly as I want?
When using an interface like phpmyadmin/heidisql or others, I have this tables:
account
account_post
account_post_status
account_post_type
account_status
account_type
Ideal for me is this:
account
...
- 1
0
votes
0
answers
71
views
SQL Server table design handling ubiquitous information
I have a doubt about table desgin in SQL Server 2019.
Usually i try to map tables with each entity in my "conceptual model", so as usual i do that and create tables for user, articles, ...
- 368
0
votes
1
answer
45
views
MySql Important Linux System Configuration [closed]
Last month I'm trying to find a "formula" for optimal Linux system configuration for MySql installation. But I didn't find anything related in those fields:
Should I disable swapping ?
...
- 101
0
votes
1
answer
3k
views
Determining data retention period
I have an application that has generating data for the past 15 years. Currently, no retention period is configured so the data size grows day by day. I would like to add a retention period to the data ...
0
votes
1
answer
211
views
MySQL: Does the default value checked by the `CHECK` constraint?
Say I have the following schema, would the default value https://sample.com be checked by the CHECK (Website LIKE "https://%")?
CREATE TABLE Company (
AccountID varchar(25),
Name ...
- 131
0
votes
1
answer
79
views
MySQL: Is it best practice to check whether some column is NULL by `NOT ISNULL(column_name)`?
Given the following schema, my question is that since I have already added the NOT NULL to IsBanned, would I have to check whether it is not null using the syntax NOT ISNULL(IsBanned)? And what's the ...
- 131
0
votes
1
answer
3k
views
MySQL: Should I add NOT NULL UNIQUE to the field for primary key?
Hi I'm completely new to MySQL, I'm creating a database and my question is that:
Should I add NOT NULL UNIQUE to the field for primary key?
For example I have:
CREATE TABLE Account (
AccountID ...
- 131
0
votes
1
answer
439
views
is it safe to create mysql database by just creating its directory?
context: I want a separate encrypted filesystem per database (to have a separate key) on MySQL 5.7 but if I mount it before I execute CREATE DATABASE foo it fails with error database exists, and ...
2
votes
2
answers
1k
views
Are SQL unit tests supposed to be so long?
I am writing stored procedures with some non-trivial business logic. I am trying to unit test them, but the actual tests end up being quite long (shorter ones starting at 40-50 LoCs, using commonly 4 ...
- 103
0
votes
2
answers
216
views
Best approach for a one-to-many list - how to store/acces some related count info?
I have a table with persons, and other with interactions, notes and tasks, all related to person(s).
I need to have quick access to:
last interaction date.
number of interactions in the past 1,6, 12 ...
- 103
0
votes
1
answer
89
views
Leaf Joins in Data Warehouse Design
Upon modeling my very first data warehouse model (for historical stock market data), I stumbled across a data relationship that I don't know how to best account for. The fact tables include multiple ...
1
vote
2
answers
175
views
What is the best practice location for Memorize Optimization (OLTP) files?
Best practices for log and mdf files is to split across drives (e.g. G:\SQL Data & I:\SQL Logs).
What are the best practices for memory optimization files? Should they reside on their own drive? ...
- 113
0
votes
0
answers
671
views
Multi-Tenant Database Design - Tenant-specific Fields
I am working on an application modernization project. We run an application which keeps track of requests for medication for patients of multiple companies.
One common design issue we run into keeping ...
- 1,086
2
votes
1
answer
372
views
Foreign Key Constraint into an different database
An SQL server hosts all the data for multiple entities (e.g sub-companies or organizational units OU).
An "enterprise" database holds the "enterprise" data such as configuration ...
-1
votes
1
answer
505
views
How would I track price items from different stores over time? [closed]
I am trying to design a database for a price comparison application, where I get data from different stores of a similar products, so my database needs to compare a price of the same product from ...
0
votes
1
answer
197
views
What are the main disadvantages of adding relationship between tables at later date?
During early development I tend to create tables using running numbers as Primary Key, although there is formally a real ID column for each table.
I tend to forget mapping the relationship between ...
- 113
0
votes
1
answer
2k
views
Is it considered good or bad practice to use default values for parameters in stored procedures/functions? [closed]
TL;DR VERSION
Is defining default values for parameters in a PostgreSQL function considered good practice, or is it something that is generally frowned upon?
Related follow-up question: If/when using ...
- 445
1
vote
2
answers
134
views
Best Practices for Separating Content from Presentation in SQL
A common and useful paradigm of programming is to separate content and presentation. One system deals with building the result, another system deals with outputting it.
You see this with HTML vs CSS. ...
- 171
0
votes
2
answers
130
views
SQL Server sharing drive location
DBA newbie here. Did some searching online but cannot find a definite answer on this.
Is it acceptable to share the drive where .MDF files reside with other non-SQL Server files such as applications ...
1
vote
1
answer
96
views
SQL Server evaluates where condition before or during the run?
I have a Stored Procedured that looks similar to this:
CREATE PROC Campaign.STP_CampaignTransaction_Get
(
@id INT
@first_name VARCHAR(50)
@last_name VARCHAR(...
- 111