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.
379
questions
0
votes
1
answer
25
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
69
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
-4
votes
1
answer
42
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
- 1
1
vote
1
answer
111
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
1
vote
1
answer
488
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 ...
- 13
1
vote
1
answer
258
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 ...
- 421
-3
votes
1
answer
45
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
141
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 (...
- 358
0
votes
1
answer
22
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
1
vote
0
answers
23
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:
...
- 111
3
votes
1
answer
49
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 ...
- 31
1
vote
0
answers
234
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.
...
- 21
-1
votes
2
answers
32
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
631
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
85
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
28
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
54
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
20
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
308
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
372
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
2
votes
1
answer
2k
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 ...
- 153
1
vote
1
answer
491
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
729
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
72
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.
...
- 355
0
votes
1
answer
609
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
419
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
66
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, ...
- 358
0
votes
1
answer
33
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
2k
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
158
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
54
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
2k
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
268
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
905
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
158
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
73
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
146
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
454
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,031
2
votes
1
answer
261
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
363
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
145
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 ...
- 375
1
vote
2
answers
118
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. ...
- 161
0
votes
2
answers
79
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
61
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
1
vote
0
answers
75
views
Best practices for users credentials [closed]
I am designing a C# client to connect to a MySQL DB and am having second thoughts on my design.
The users have credentials in order login in the C# client, so now it is designed like this:
1 - the C# ...
- 19
0
votes
1
answer
357
views
Best practice for exposing record IDs to API users
I am extending an API to include the ability to retrieve and update a specific record after the record is created by the user.
Currently, these records are created with an auto generated integer key,...
- 103
0
votes
1
answer
109
views
How to allow only a certain type of foreign key in a table
I'm designing a new DB. I have employee and order table. order table has deliverer_id to indicate who delivered the order. But only employee who is "deliverer" should be allowed.
I thought of 3 ...
- 499
1
vote
2
answers
2k
views
Database Design for Pokémon Review
I am trying to learn more about Relational Databases. So, I did a pokemon database gathering the stats available on the Smogon website. I have done some research thus far and would like to get some ...
- 11
1
vote
3
answers
247
views
MS SQL 2008 R2 - How to use a production database through dev/test environments or how to copy a SQL schema and all its data to another
We currently have 3 Windows Server 2008 R2 VM identically build for Sage x3 v6.5, one for production/formation/budget database, a second one for QA (qualification) and a third one for dev & tests. ...
- 11