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.

43 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
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 ...
BooRuleDie's user avatar
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: ...
Basil's user avatar
  • 131
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 ...
fditz's user avatar
  • 121
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 ...
CuriousGeorge's user avatar
2 votes
0 answers
179 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 ...
jasoncrider's user avatar
2 votes
0 answers
631 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. ...
James Jenkins's user avatar
2 votes
2 answers
57 views

Schema For "Other - Add Comment"

Many user forms contain an option for "Other - Add Comment" where the user can provide an item not found in the given list. In other words, the developer sets the list of items. The user must either ...
Steven's user avatar
  • 319
2 votes
0 answers
431 views

What are best practices for managing DB record versioning with cross-reference tables?

In our system, we need to be able to track the changes to our objects and to be able to generate a history file for each such object. For the most part, this is straightforward: We can either create ...
JollyGood's user avatar
2 votes
1 answer
75 views

SQL application connection using one Windows user

We are currently looking to update an old application security structure and would like to go for an approach where the application connects to SQL via one Windows login with the required Read/Write ...
Ron's user avatar
  • 21
2 votes
0 answers
55 views

What is the best way to keep track of 1000 RDS hosted databases?

I want to know what is 'best-practices' for managing an extreme number of RDS instances. I have no problem managing five. But managing dozens or hundreds, that's another task. I'm thinking use a ...
Michael Occhipinti's user avatar
2 votes
0 answers
812 views

Is not having 'named' timezone support common in MySQL?

I'm trying to set the timezone the database uses using: "SET time_zone = 'UTC'; Whilst this works on my development server it doesn't work on my staging server; I get this error: General error: ...
Brett's user avatar
  • 385
2 votes
0 answers
413 views

going from heirarchical XML to heirarchical SQL tables then SQL to c# objects to go to user view, how should I store the data in the databases?

Hi I'm getting my data from a web service using their getData call and it comes in as fairly chunky heirarchical XML, first everything all at once, like <allData> <product1> <...
fa1c0n3r's user avatar
  • 617
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 ...
AmineBinOthman's user avatar
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 ...
alx9r's user avatar
  • 431
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. ...
JaySmi's user avatar
  • 21
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 ...
aduensing'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
0 answers
33 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 ...
MCunha98's user avatar
1 vote
0 answers
36 views

Strategy to manage foreign key from another system/database

There is a system in the company which stores all the consultants working for the company. Our project needs to store the consultants assigned to clients. We use the id of the consultant in our ...
Tathagat's user avatar
1 vote
0 answers
72 views

Best practice for large database

I have a mySql database and produce webpages through use of php. I have a database that in simplified form collects data of the form: date|name|score which is unique for date|name. From this ...
Geoff's user avatar
  • 283
1 vote
0 answers
72 views

Is the source system typically one of the fields used to uniquely identify business key?

One of the key components of designing a data vault is identifying enterprise-wide unique business keys ("business key" AKA "natural key"). It's not enough to use OrderID to identify records in an ...
Jon of All Trades's user avatar
1 vote
0 answers
300 views

What is the correct way to store multiple values of an entity's property with a "primary" or "default" one?

In previous database model I've had an entity that had a simple name property. Later I'd find out that a single name was no longer sufficient - rarely entities had multiple names and it was time to ...
Amunak's user avatar
  • 111
1 vote
0 answers
2k views

A "best practice" Postgresql function template for ETL processes

I use SQL Server in my day job, and have a personal project to build a star schema data warehouse in Postgres 9.4 on Ubuntu 14.04 to extend my knowledge (it'll be approximately 300GB uncompressed). I'...
Adrian Torrie's user avatar
1 vote
1 answer
106 views

How do I abstract a WHERE clause condition?

I'm wondering how a condition in a commonly used WHERE clause can be abstracted away so that it can be safely changed. Say I have data like ColA | ColB | ColC ================== 1 | 2 | ABC ...
Kevin Shea's user avatar
1 vote
0 answers
1k views

What's is the best way to get incremental changes from production to incrementally changed development database?

I came to a new dev team where all development is on the same (instance) of an SQL Server (2005) database once in 1-2 days restored from a current full backup of production database replacing a dev ...
Fulproof's user avatar
  • 1,392
1 vote
0 answers
310 views

Enforce a hard limit on write execution time during Amazon RDS write stalls

I am trying to recover from the situation where Amazon RDS write operations stall (some discussion on why this is here). If I show full processlist, I will see something like this: 318263 myapp1 ...
esilver's user avatar
  • 111
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 ...
mstrom's user avatar
  • 133
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 ...
JE_2123A's user avatar
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 ...
Alsaraha's user avatar
0 votes
0 answers
69 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
  • 368
0 votes
1 answer
189 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
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 ...
Baptiste Mille-Mathias's user avatar
0 votes
2 answers
194 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
81 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
0 votes
0 answers
616 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
0 votes
0 answers
47 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 ...
Lou's user avatar
  • 181
0 votes
1 answer
133 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 ...
Sankalp Mishra's user avatar
0 votes
0 answers
172 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. ...
vacip's user avatar
  • 123
0 votes
1 answer
36 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, ...
xicotaslb's user avatar
0 votes
0 answers
379 views

Best practice for record snapshot? (versioning?)

What's the best practice for storing a record snapshot? Use case example: Assume a user bought a product in an online store. It is a pleasant experience that the user can view a copy of what he saw ...
otong's user avatar
  • 101
-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 ...
Delicious Bacon's user avatar
-1 votes
1 answer
521 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 ...
Gravitate's user avatar
  • 421
-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
Debbie S's user avatar