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.
4
votes
2answers
528 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 ...
2
votes
2answers
665 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?
...
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
1answer
442 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 ...
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
...
2
votes
2answers
177 views
Differences between development and live database setups
I was wondering if there are any best practice recommendations on setting up databases in a dev environment versus a live environment with regards to configuration.
Should they be as similar as ...
2
votes
3answers
855 views
What are the best practices for triggers to maintain a revision number on records?
I have an audit trail implemented on an application database that captures old value, new value, modification time, and user inserting it into another table. I'm now wanting to add a revision number ...
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 ...
3
votes
1answer
454 views
Is it better to delete then insert, or to update then insert in SQL server?
I'm using SQL server 2005. I have a table that is basically a list of ids with timestamps that is recording the last time an event happened for that id.
I have a list of ids in a table variable, and ...
3
votes
2answers
277 views
Does adherence to the DRY principle justify dynamic SQL?
Suppose I have two queries that will be frequently run against my database:
SELECT
UserID,
UserName,
UserGender
FROM Users
WHERE UserID = @User
SELECT
UserID,
UserName,
UserGender
FROM ...
3
votes
4answers
910 views
Should I convert empty varchar values to NULL?
I have a stored procedure which accepts various varchar parameters. The middle tier code which calls the procedure has not been consistent in terms of the values it submits. For example, sometimes a ...
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....
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
...
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...) ...
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 ...
1
vote
1answer
718 views
Inner cursor performance issues
Before I tried to avoid using cursor because of performance issues. But now I have to do some calculations to reach some goals. I have a stored procedure that has a code like this:
DECLARE ...
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 ...
1
vote
1answer
346 views
What are the best practices for ETL of data from one OLTP system to another, Part 2?
Ok, I originally started a question a ways back called "What are the best practices for ETL of data from one OLTP system to another?". And as you can probably tell from most of my questions so far, ...
5
votes
1answer
886 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 ...
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 ...
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 ...
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 ...
1
vote
2answers
258 views
Determining sizes for Separated Partitions of programs, data and logs in SQL Server
As per the recommendations of the CIS benchmarks for SQL Server, I want to create separate partitions for the data, the programs and the logs on my SQL Server dedicated server.
Question is, what is ...
2
votes
1answer
533 views
What are the best practices for ETL of data from one OLTP system to another?
First off, I want to post that I am a developer and not a DBA, so I am trying to approach this from the standpoint of what is the best thing to do, rather than just assuming I can do everything ...
3
votes
2answers
329 views
What is the best practice for mapping from natural keys to integer-based keys? (ETL)
This question originates more from an "enterprise architecture" point of view. (Reason being, our company is just starting to get into data management, and actually having DBA's -- I know, horrific ...
1
vote
4answers
231 views
Best way to automate running several MySQL commands
We have several core db tables on our system and allow our employees access to these tables via views that just show them the data they need to see.
We created these views in a new db specific 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 ...
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
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, ...
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 ...
5
votes
2answers
338 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 ...
12
votes
1answer
289 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 ...
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 ...
