Database Administrators Stack Exchange
Database Administrators Stack Exchange

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.

A best practice will not always be superior for every case. In addition, a best practice can be superseded when another best practice is determined to be better over time.

383 questions
8
votes
3 answers

"Table of Constants" - is this common practice?

College SQL class, using the book "SQL Fundamentals" by John J. Patrick. In the third chapter, he talks about using a "table of constants" to add columns to a select statement, where all rows have the same value. For example, if you have table…
mjfgates
  • 183
  • 1
  • 1
  • 4
6
votes
3 answers

"SELECT *" why is it an antipattern

On multiple questions here and on stack overflow i saw people saying in the comments and answers that select * from table is almost always an antipattern without any explaination why. Althrough I can sort of deduce why it is an antipattern. I may be…
Thomas E.
  • 61
  • 3
5
votes
3 answers

How to store/organize all of these scripts?

I am about 10 months in to my DBA role and I am already accumulating quite the collection of scripts. Does anyone have any best practices or recommendations on how to store all of these scripts? I manage about 70 instances of SQL server and am…
TattooedDBA
  • 153
  • 2
  • 10
4
votes
1 answer

What are the pitfalls when storing data retrieved from an api

I can get some financial data from our vendor via their API. In this case API is too strong a word. Basically we can get only an array of orders for a given time range. What we paid them for the product, what they charges us for shipping, etc. To…
microsaurus_dex
  • 43
  • 2
3
votes
3 answers

What are the design alternatives when having multiple "type" tables

I am working on a database design and see myself with lots of *_type tables (e.g. user_type, product_type, etc.) where the structure of these tables is the same, basically: user_type ( id int pk label char ) I could simplify this by doing…
Adam
  • 33
  • 3
1
vote
0 answers

Should I create a record for every user or wait for user interaction

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 in my application that allow the user to display…
clementescolano
  • 111
  • 3
1
vote
1 answer

Is introducing a where condition into the join clause bad practice

In the query below, the condition and r.master_seq=206 is used on the on clause as a join condition. In the programme code, the value for r.master_seq is set by a parameter. My concern is that I'm not sure if it's ok to mix in this…
Ascendant
  • 429
  • 2
  • 4
  • 15
1
vote
1 answer

Dealing with Difficult DBAs

At one point, test database deployments within my department were handled directly by the database developers. The typical process was that the developer would unit test their changes in the development environment, then: 1) Migrate their changes to…
Anonymous
  • 29
  • 1
0
votes
1 answer

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 should I make them in one table colors and images…
Alsaraha
  • 1
  • 2
0
votes
1 answer

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 else or to have two tables one for products and one…
Alsaraha
  • 1
  • 2
0
votes
2 answers

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 months. number of notes (Total) number of…
SharpBCD
  • 103
  • 1
  • 3
0
votes
1 answer

2 Different database or 1 Single database

I have a Windows 7 machine running MySQL database and I have a Linux machine running PostgreSQL database. Preferably I would like to have only 1 machine housing both database as currently they are co-located in a datacenter which counts the…
gnulab
  • 1
-1
votes
2 answers

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, payment_source, table_source, table_source_id, package_id,…
Roman
  • 103
  • 3
-3
votes
1 answer

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/email. Beyond that there is likely different set of…
Rusi
  • 95
  • 2