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
1
vote
1answer
32 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 ...
0
votes
2answers
51 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. ...
0
votes
1answer
36 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 ...
0
votes
0answers
38 views

What Strategies are most advisable for managing users and their privileges in an AWS RDS Database?

Whenever a new database is created, best practices dictate that we should deploy alongside with it several users for several different activities. For example, a typical (at least to me) arrangement ...
0
votes
1answer
54 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 ...
0
votes
0answers
58 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, ...
0
votes
1answer
26 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 ? ...
0
votes
1answer
140 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
1answer
20 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 ...
0
votes
1answer
37 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 ...
-1
votes
1answer
340 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 ...
0
votes
1answer
22 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
2answers
639 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 ...
0
votes
0answers
16 views

Which design could be better for scale a MongoDB query for this usecase

Context Imagine that you have two MongoDB collections, one called nodes and the other called mirrors. Each mirror is replica of a node. So, in case the node is removed (for whatever reason), you ...
0
votes
2answers
29 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 ...
0
votes
1answer
26 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
2answers
101 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? ...
0
votes
0answers
68 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
vote
1answer
34 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
1answer
58 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
1answer
65 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 ...
0
votes
1answer
484 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 ...
1
vote
2answers
53 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. ...
0
votes
2answers
39 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
1answer
32 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(...
1
vote
0answers
45 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# ...
0
votes
1answer
87 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,...
0
votes
1answer
42 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 ...
0
votes
2answers
637 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
vote
3answers
181 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. ...
1
vote
2answers
1k views

How to restart MS SQL server right, what is best practice to do that?

I`m quite new to DB administration. I have a problem when I restart SQL server, one of the databases goes into Recovery mode and automatically repairs the DB in 30 min. I don't know why this happens....
0
votes
2answers
3k 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 bee 80% of the RAM. I wonder if there is any best practice for this configuration that could allow Oracle ...
0
votes
0answers
36 views

How can you effectively visualise a complex relational database?

I have an Access database which contains all the data from an open-source Japanese dictionary, JMdict. The database contains 24 linked tables, so reading it is complicated. The reason for this is that ...
4
votes
1answer
731 views

SQL Server Linked Server Best Practices - Step by Step

Hello and thanks for stopping by. I'm an accidental DBA looking for some guidance in creating Linked Servers the correct way. MS provides complete descriptions of the datatype and descriptions of ...
0
votes
1answer
193 views

DB Structure: one table to hold all domain values? [duplicate]

One database I recently start working with was designed with one main table containing all possible values, and foreign keys in other tables are specifying the nature of each of those items. CREATE ...
41
votes
7answers
8k views

What's a good use case for SELECT * in production code?

Out of habit, I never use SELECT * in production code (I only use it with ad-hoc scrap queries, typically when learning the schema of an object). But I ran across a case now where I'm tempted to use ...
-1
votes
1answer
82 views

Is there a best practice design for handling different types of the same object which have different properties?

This may be a rather naive question and I am not even sure if I am asking it in the best way, so my apologies if not. I would like to know if there is a standard, or best way to handle cases where ...
0
votes
1answer
60 views

Mysql Sharding. Application Layer vs Mysql Layer

I am going to create shards in mysql table and I am stuck at a dileman whether to go for application layer sharding or let MySQL handle it. I mean, wether I should be figuring out which shard to go ...
0
votes
0answers
69 views

Condition table theory

Is there a good way to create a "table of conditions"? I have a huge table, where 4-5 columns are considered in a set of rules, and based on those columns default values must appear in other columns. ...
0
votes
1answer
35 views

Database Architecture Identifiers

I am kind of new around DBA world, i am currently working with JMeter(front/back end performance testing) but when i have spare time (which is a lot lately..) i try to learn about DBA Development, ...
1
vote
0answers
30 views

Centralizing Databases

I have a very challenging scenario at hand and would like to hear suggestions on the solution I am considering implementing. I have several databases that have similar (non-identical) structures and ...
2
votes
1answer
210 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 ...
3
votes
1answer
90 views

SQL Server 2016 SP1 + strategy for long term version retention (snapshots, temporal tables, other?)

We have a data mart fed from an upstream data warehouse that we generate reports out of for external parties. I have a business requirement to preserve the state of the database for each month end to ...
6
votes
1answer
482 views

Is it safe to use Strings instead of SqlStrings for CLR function input parameters?

I have a CLR scalar UDF implemented through C# code. I've noticed that using the String data type for input parameters significantly improves performance compared to the SqlString data type. In ...
3
votes
2answers
648 views

SQL table with same name as catalog view

When naming tables in SQL I try to stay away from SQL reserved keywords, but today a colleague questioned the use of Events as a table name. They said, anything that turns green in SSMS shouldn't be ...
0
votes
1answer
121 views

Flash storage and auto-grow

Our databases files (data and log) of (a lot of) SQL Servers are hosted on flash storage (SAN), what would you use as auto-grow property for databases having size round 500GB (data) and 150Gb (log)? ...
2
votes
0answers
134 views

Questions and advice for complying with Federal tax information (FTI) Security Guidelines for Federal, State and Local Agencies per Publication 1075

I've been able to find writings on compliance topics (e.g. HIPAA, SARBANES-OXLEY, PCI DSS) but I haven't found much on FTI (Federal tax information) and Publication 1075 outside of the IRS website ...
4
votes
1answer
3k views

Added .Net assembly to SQL CLR without turning TRUSTWORTHY ON

I'm trying to add "System.Messaging.dll" using the guidance provided by Solomon Rutzky in his post Assembly deployment with permission UNSAFE or EXTERNAL_ACCESS using asymmetric key but I'm failing at ...
0
votes
1answer
153 views

Do SET Statements happen in order?

Since I could not find it out here, do set statements happen in order scripted? Below is query I found. Would I get the same result every time it ran? DECLARE @Num VARCHAR(10) = '10'; DECLARE @...
2
votes
0answers
514 views

Disable and Rename sa, years after build

Answers and comments at a recent popular question Does changing "sa" password require a SQL restart (in mixed mode)? indicate that renaming and disabling sa at build time is a best practice. ...

1
2 3 4 5
8