Skip to main content

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
3 answers
10k views

Best Practice for Oracle SGA and PGA Memory Components Size

I have a Server that has 128GB RAM. I have read here and there that the size of SGA + PGA can be 80% of the RAM. I wonder if there is any best practice for this configuration that could allow Oracle ...
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 ...
177 votes
20 answers
62k views

Should developers be able to query production databases?

Should developers be given permission to query (SELECT / read only) production databases? The previous place I worked, the development team had the db_datareader role; where I work now the ...
27 votes
7 answers
8k views

Is table aliasing a bad practice?

I remember learning to do this in a DBMS course for Master of Information Services students. To save yourself some typing, you can type: SELECT t1.id, t2.stuff FROM someTable t1 ...
2 votes
2 answers
1k views

How to delete foreign key with lots of references

Table B have several thousand records that references table A with a foreign key. The application sends a request to delete the row from table A. It's important that the synchronous operation would ...
0 votes
1 answer
387 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 ...
1 vote
1 answer
795 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 ...
1 vote
1 answer
482 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 ...
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 ...
-1 votes
1 answer
76 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 ...
74 votes
4 answers
58k views

Function Performance

Coming from a MySQL background, where stored procedure performance (older article) and usability are questionable, I am evaluating PostgreSQL for a new product for my company. One of the things I ...
0 votes
0 answers
39 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 ...
2 votes
2 answers
62 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 ...
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) | +-----------+------------------+---------...
3 votes
1 answer
3k views

What's better here - a single column or multicolumn primary key?

Let's assume you have a table groups and a table item. Each item belongs to exactly one group. It is an inherent part of that group. An item cannot exist outside of a group and it cannot be moved to ...
1 vote
1 answer
31 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 ...
9 votes
2 answers
1k views

How can I losslessly decompose this table?

I have a table with the following PK (primary key): child_id integer parent_id integer date datetime child_id and parent_id are FKs (foreign keys) to entity tables. The child table also ...
2 votes
3 answers
85 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: ...
-3 votes
1 answer
44 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
21 votes
3 answers
19k views

What are the best practices regarding lookup tables in relational databases?

Lookup tables (or code tables, as some people call them) are usually a collection of the possible values that can be given for a certain column. For example, suppose we have a lookup table called ...
1 vote
1 answer
229 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 ...
120 votes
3 answers
89k views

Is it a bad practice to always create a transaction?

Is it a bad practice to always create a transaction? For example, it is a good practice to create a transaction for nothing but one simple SELECT? What is the cost of creating a transaction when it ...
1 vote
1 answer
816 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 ...
-3 votes
1 answer
64 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/...
2 votes
1 answer
208 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 (...
24 votes
1 answer
84k views

Grant access to all tables of a database

I recently wanted to share regular access rights with one user of a server and I realized that a simple CREATE USER and GRANT ALL ON DATABASE commands didn't let him run a simple SELECT on the data. ...
0 votes
1 answer
27 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 ...
4 votes
1 answer
57 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 ...
2 votes
0 answers
24 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: ...
1 vote
0 answers
486 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. ...
5 votes
2 answers
40k views

When should I NOT set the Maximum Server Memory option in SQL Server?

In SQL Server, I normally set the Maximum Server Memory setting to one of the popular best practices, such as leaving 10% or 4GB, whichever is higher. http://www.brentozar.com/archive/2012/11/how-to-...
-1 votes
2 answers
37 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, ...
3 votes
2 answers
722 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 ...
0 votes
1 answer
124 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 ...
0 votes
1 answer
34 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 vote
1 answer
769 views

Which of these structures are good for maintaining historical data?

I am creating an application which is being used to send reminders. Here is the requirements. We have set of Students. Every student has a Student Pass, this need to be renewed in every month. ...
-1 votes
1 answer
57 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
22 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 ...
2 votes
1 answer
2k views

tempdb on raid1 isolated or raid10 shared with high write db

PROPOSAL 2 8x600gb raid 10 mdf array 4x600gb raid 10 ldf array PROPOSAL 3 8x600gb raid 10 mdf array 2x600gb raid 1 ldf array 2x600gb raid 1 tempdb mdf/ldf array READ/WRITE STATS ON CURRENT SAN db....
22 votes
3 answers
16k views

Bad Practice to Store Calculated Data in Database?

Is it bad practice to store calculated data in each row, or is it better to calculate at the application layer with every read from the database. Storing in the database avoids the need to calculate ...
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 ...
280 votes
7 answers
154k views

When should I use a unique constraint instead of a unique index?

When I want a column to have distinct values, I can either use a constraint create table t1( id int primary key, code varchar(10) unique NULL ); go or I can use a unique index create table t2( id ...
3 votes
2 answers
2k views

AUTO_UPDATE_STATISTICS and FULLSCAN in SQL Server 2008 R2

Is it possible to force FULLSCAN when statistics are updated automatically by SQL Server 2008 R2? If not, is a planned UPDATE STATISTICS WITH FULLSCAN the best way to keep statistics up-to-date? ...
3 votes
1 answer
362 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, ...
0 votes
1 answer
515 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 ...
103 votes
18 answers
43k views

Is adding the ‘tbl’ prefix to table names really a problem?

I’m watching some Brent Ozar videos (like this one, for instance) and he suggests not prefixing tables with ‘tbl’ or ‘TBL’. On the internet I found some blogs saying it adds nothing to documentation,...
4 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 ...
23 votes
5 answers
15k views

Is it still best practice to avoid using the default ports for SQL Server?

Historically, it has been recommended not to use the default ports for connections to SQL Server, as part of security best practice. On a server with a single, default instance, the following ports ...
13 votes
3 answers
35k views

Storing a Billing Address Best Practice in Orders Table

Can someone help me understand this user's answer for a CustomerLocation table. I really want a good method for storing addresses in the orders table. What I'm looking for is how I can set up my ...
0 votes
2 answers
77 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. ...

1
2 3 4 5
8