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.
63
votes
18answers
8k 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 ...
31
votes
11answers
2k views
How could DBAs be more 'programmer friendly'?
The answers and comments on the dba.se version and programmers.se version of the question "What are the arguments against or for putting application logic in the database layer?" are very revealing ...
19
votes
5answers
4k views
Best practices on common person fields (Name, email, address, gender etc…)
What are the most common best practices on length and data type on common fields like:
First Name
Last Name
Address
Email
Sex
State
City
Country
Phone Number
etc....
16
votes
4answers
1k views
Unit testing of stored procedures
I've been considering this for quite a long time now.
The basic question is: how to unit test stored procedures?
I see that I can set up unit tests relatively easily for functions in the classic ...
16
votes
3answers
4k 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 ...
13
votes
6answers
10k views
What is the recommended RAID configuration for an Oracle database?
RAID (Redundant Arrays of Inexpensive Disks) comes with different configurations (RAID-0, RAID-1...). What is the recommended RAID configuration that I should set up and use when installing an Oracle ...
12
votes
4answers
2k views
Where should you define foreign keys?
Is it better to define foreign keys in the database or in the code part of an application?
12
votes
1answer
2k views
Best practices for schema changes and data migrations to a live database without downtime?
How do you make schema changes to a live database without downtime?
For instance, lets say I have a PostgreSQL database with a table including various user data like email addresses etc, all ...
12
votes
1answer
295 views
Best practices for connecting DBs that are in different geographical regions
We are about to set up SQL Servers in different countries. We need to link them, but we don't have to have a direct link (as in linked server). In other words, they can be loosely coupled.
Is it ...
11
votes
3answers
632 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
...
10
votes
4answers
4k views
Should dbo schema be avoided?
When it comes to the dbo schema:
Is it a best practice to avoid using the dbo schema when creating database objects?
Why should the dbo schema be avoided or should it?
Which database user should own ...
10
votes
3answers
2k views
PostgreSQL Stored Procedure 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 ...
10
votes
2answers
3k views
What does a best-practice SQL Server maintenance plan look like?
I am a student from Fontys University in Eindhoven, and I am currently carrying out a series of interviews to help with the development of a SQL Server tool and I would like to get feedback from the ...
9
votes
3answers
20k views
Sql Server Maintenance Plan - Best Practices on Tasks and Scheduling
I am tasked with devising a maintenance plan for our Sql Server 2005 databases. I know for backups I want to do a daily full database backup and transactional log backups every 15 minutes. My problem ...
9
votes
5answers
4k views
Splitting Tables in MySQL. Good practice?
I have started working on an existing project and the previous developer had split up a table into 10 separate tables with identical schemas but different data.
The tables look like this:
...
9
votes
3answers
283 views
Any pointers on reducing my privileges in production but not making my job excessively difficult
Running SQL Server 2005 and 2008 on Windows 2008 R2.
We're going to be reducing privileges in production for developers - and I'd like to do the same for myself as a DBA, limiting rights to ...
8
votes
2answers
8k views
Database model with users, roles and rights
I have a database model with a user table and role table. I want to control the access (rights) to up to 10 different elements. The access can be granted to either a role or a single user.
Below is ...
7
votes
3answers
2k views
What is the best method to add error handling in SQL 2005 stored procs?
What’s a good way to make stored procs robust enough that they can scale very well and also contain error handling?
Additionally, whats the best way to handle multiple error scenarios in a stored ...
7
votes
4answers
1k views
Help me choose a RAID level combination for a SQL Server 2008 instance
I am going to rebuild one IBM 3400 server from scratch. This server is dedicated to a SQL Server 2008 instance running on Windows 2008 R2.
I am going to make new RAID configuration. I have 6 SCSI ...
7
votes
1answer
420 views
Why does the fill factor in SQL Server default to 0 (100%)?
I understand fill-factor, pages and index structure and I therefor understand why a 100% fill factor is a rare best practice. So why does it default to 0 (or 100% ) by default? Why not 90 or 95?
Is ...
7
votes
2answers
230 views
Can I losslessly decompose this table?
I've stumbled across a database design problem that's out of my league, and my go-to DBA guru is off on fire drills.
In essence, I have a table with the following primary key:
child_id integer
...
7
votes
1answer
475 views
How to setup local database development process for small web team?
Background
I am working on creating a new development process for a small web team of about 4 programmers and 4 designers, with the obvious potential to grow the team in the future.
Our product is a ...
6
votes
2answers
264 views
Is there any difference between putting a column alias at the start or the end of the column definition?
I've always seen and written my column aliases as
SELECT 1 as ColumnName
but today came across a query that used
SELECT ColumnName = 1
Is there any difference in how these two queries get ...
6
votes
5answers
375 views
Over use/correct use of schemas?
Having asked This question on Stackoverflow, I wondered where what I have done is correct/best practise.
Basically, every object that I create is going into a schema with the schema name reflecting a ...
6
votes
2answers
2k views
What is more efficient, a where clause or a join with million plus row tables?
We run a website that has 250MM rows in one table and in another table that we join it to for most queries has just under 15MM rows.
Sample structures:
MasterTable (Id, UserId, Created, Updated...) ...
6
votes
2answers
259 views
Is it safe to run antivirus software on my database servers?
Some implementations of antivirus software are known to Microsoft to cause a variety of problems for SQL Server, and Microsoft has a support article dedicated just to picking the right antivirus ...
6
votes
3answers
155 views
How to handle reporting of an extreme amount of databases in SQL Server 2005?
I'm looking for some advice on how to handle reporting for our environment. We currently have 16 servers with 20 instances of SQL Server 2005. We have over 6,600 databases and growing across these ...
6
votes
4answers
161 views
Database Design - different objects with shared tagging
My background is more in web programming rather than database administration, so please correct me if I'm using the wrong terminology here. I'm trying to figure out the best way to design the ...
6
votes
1answer
544 views
What should every DBA know? [closed]
As a SysAdmin who "wears all the hats", I find myself increasingly pulled into database projects. I took one database course back in college and since then I've always scoured resources online (like ...
5
votes
2answers
162 views
Defining constraints in `CREATE TABLE` statements
Recently I have been using a Database Abstraction Layer built by a Python web-framework called web2py (click for their DAL syntax). They include the option to include your constraints within the ...
5
votes
4answers
2k views
Parallelism Best Practices
What are the best practices with setting parallelism in general? I know that SQL Server defaults to 0 to use all available processors, but in what instance would you want to change this default ...
5
votes
2answers
237 views
Best practices for change management with indexes
Our IT shop is first starting to build a group of DBA's. All of us (myself included) have come over from the application development/architecture world, so the DBA world is still fairly new to us.
...
5
votes
2answers
339 views
Best practices to run time offset delayed replication
I want to setup replication system on postgresql like #1 MASTER SERVER and two replicated servers: #2 SERVER in realtime (for load balancing), #3 SERVER delayed with some time offset (for example 2 ...
5
votes
2answers
2k views
Storing time with milliseconds in database
Just a quick question regarding storing "time" in a database. I'm logging the time of users runs which are in the format 00:00:00:00 (hours, minutes, seconds, milliseconds).
I was originally going to ...
5
votes
3answers
165 views
Database Indexing - Maintenance Jobs
I have created a script that runs every night to rebuild & re-organize indexes based on the fragmentation, Indexes with Fragmentation > 30% are rebuilt, Indexes with Fragmentation 10% - 30% are ...
5
votes
1answer
405 views
Stored Procedures Best Practices: Fenced or Unfenced?
I believe I understand the reasons behind fenced and unfenced stored procedures.
Fenced run "outside" of the database (in our case DB2) so as to prevent possible corruption of the database engine ...
5
votes
1answer
444 views
How to handle “reference tables” in CouchDB?
What's the best way to handle "reference tables", tables that contain static data, for example a list with various kinds of ingredients for food?
Multiple documents, one for each ingredient
One ...
5
votes
1answer
887 views
How to design a schema for a site that accepts logins from multiple sites (e.g. facebook connect, google, flickr, etc..)? [closed]
I'm building a site ("The Site") and I'm considering how to design the DB schema for it.
I have a few scenarios that will affect the outcome:
The user can choose to register a native account on the ...
4
votes
4answers
2k views
Over use of Oracle With Clause?
I'm writing many reporting queries for my current employer utilizing Oracle's With clause to allow myself to create simple steps, each of which is a data oriented transformation, that build upon each ...
4
votes
3answers
649 views
“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 ...
4
votes
2answers
534 views
Best way to handle dates prior to 1000 A.D. in MySQL?
I am creating a database for records that extend prior to 1000 AD, but MySQL Date and DateTime fields only support dates starting at 1000.
Is there a way that would be more convenient than either ...
4
votes
2answers
364 views
How to mark an item as “Expired” when it's ExpiryDate value is in the past?
I have a table of items, each of which has a Status and ExpiryDate.
What is the best way of enforcing this:
when ExpireyDate > DateTime.Now
update Status to "expired"
I could have a stored ...
4
votes
2answers
240 views
How to manage my own scripts
Every administrators, developers have their own collection of scripts (some patterns). Time to time they are dismissed from one job and they are employed to other one. Is there any good, easy used, ...
4
votes
2answers
233 views
Huge database logging of event type rows and ways to optimize it
We have a database that stores events. Events are generated at a 1000 per sec rate. We need to keep these events accessible for some years.
Usual use of these events is selecting some of them from the ...
4
votes
2answers
173 views
Best practise for copying a database from production
Currently after releasing a new major or minor version of our application, we take a copy of the production database and perform the below process:
Copy production database to test server
Obfuscate ...
4
votes
1answer
345 views
What is the optimal way to design an approval queue and audit trail for entities in SQL Server 2008 R2?
Simplified Explanation: I have three main tables: customer, phone, and customer_phone to resolve the many-to-many. I need to ensure that only admins can insert/update these tables without approval. ...
4
votes
1answer
1k views
Modelling Relationships in an E-R Diagram, Table or Foreign Key?
Should a relationship in an E-R diagram be created in the database as a table or a foreign key?
For example:
a tutor lectures a class
I see it as three possibilities:
there is a "lectures" ...
4
votes
2answers
308 views
Best way to identify orphan data?
I have a few dozen tables, that have data in them, both for the core set of data, and then the company specific data.
I can never delete the core set of data, that is global for all companies.
But i ...
4
votes
1answer
86 views
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 ...
3
votes
3answers
315 views
Is a surrogate key better than a natural key in this case
I copied this code from here:
CREATE TABLE records(
email TEXT REFERENCES users(email),
lat DECIMAL,
lon DECIMAL,
depth TEXT,
upload_date TIMESTAMP,
comment TEXT,
PRIMARY ...
