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.

Filter by
Sorted by
Tagged with
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 ...
AmineBinOthman's user avatar
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: ...
gotali4396's user avatar
-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
Debbie S's user avatar
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 ...
micahblu's user avatar
  • 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 ...
Nuxurious's user avatar
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 ...
alx9r's user avatar
  • 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/...
Rusi's user avatar
  • 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 (...
Skary's user avatar
  • 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 ...
JE_2123A's user avatar
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: ...
Basil's user avatar
  • 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 ...
David's user avatar
  • 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. ...
JaySmi's user avatar
  • 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, ...
Roman's user avatar
  • 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 ...
LizWeir's user avatar
  • 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 ...
Alsaraha's user avatar
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 ...
Alsaraha's user avatar
-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 ...
Delicious Bacon's user avatar
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 ...
clementescolano's user avatar
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, ...
marsisalie's user avatar
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 ...
Dayton Brown's user avatar
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 ...
Timur Shtatland's user avatar
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 ...
aduensing's user avatar
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 ...
fditz's user avatar
  • 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. ...
Dirk Boer's user avatar
  • 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 ...
Yousif's user avatar
  • 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 ...
Andrei's user avatar
  • 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, ...
Skary's user avatar
  • 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 ? ...
Mark Davydov's user avatar
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 ...
NameNotFoundException's user avatar
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 ...
Rainning's user avatar
  • 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 ...
Rainning's user avatar
  • 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 ...
Rainning's user avatar
  • 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 ...
Baptiste Mille-Mathias's user avatar
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 ...
IvanLoginov's user avatar
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 ...
SharpBCD's user avatar
  • 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 ...
Pit Wegner's user avatar
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? ...
Chris Gonzales's user avatar
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 ...
Kirk Saunders's user avatar
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 ...
Moshe Yalovsky's user avatar
-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 ...
PHULUSO GOVERN RAMULIFHO's user avatar
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 ...
encryptoferia's user avatar
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 ...
G_Hosa_Phat's user avatar
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. ...
pbarney's user avatar
  • 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 ...
DbaTurtle's user avatar
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(...
juanora's user avatar
  • 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# ...
Felipe's user avatar
  • 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,...
Half_Duplex's user avatar
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 ...
user2652379's user avatar
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 ...
Pilipe's user avatar
  • 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. ...
tigerblue77's user avatar

1
2 3 4 5
8