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.

learn more… | top users | synonyms

5
votes
1answer
1k 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. ...
10
votes
6answers
7k 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: ...
1
vote
2answers
19k views

How to join two table and show one query result in MySQL?

I have a table1 (records 3), and table2 (records 3). Where i have field name in both. Now i want to make a result from those two table, which will show me both table records and take only one if there ...
20
votes
1answer
6k 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 ...
2
votes
3answers
1k views

View Design Best Practices

When designing a view, would it be best to retrieve the ID fields where FK relationships exist between the joined tables the desired description field obtained through the FK id relationship both? ...
2
votes
1answer
11k views

Multiple left joins or subquery?

I'm working on joining multiple tables together to extract some data related to user "runs", this includes basics like runID, username etc but also joins to the run times and favourites. I have the ...
6
votes
2answers
6k 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 ...
99
votes
18answers
21k 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 ...
2
votes
2answers
1k views

Best practices for creating partition schemes and functions

I'm implementing partitioning for the first time in SQL Server 2008 R2. I've several large tables that I am partitioning. My main question is: Should I use the same Partition Scheme and Function for ...
5
votes
1answer
1k views

Nested queries or temp (#) tables in SQL Server

I have written a web application which aggregates and presents data from a SQL Server. The data involved is large, and there is a lot of aggregation involved. Currently, under advice from an 'expert' ...
2
votes
2answers
1k views

Is there any prevalent logging packages in PL/SQL similar to java and .Net counterparts

Normally in java or C# , I would use a following construct when try and catch errors. Bad Exception Handling in C# and Java try { // do something } catch(Exception ex) { } Good Exception ...
3
votes
1answer
270 views

Possible to add KEEPFIXED PLAN option to reduce the recompile in SqlServer2k5 on Production Env?

I'm not sure if it is good to enable the KEEPFIXED PLAN to reduce the impact of recompilation in the Production Env? I'd know if we may gain performance after that or on the other hand it will be ...
17
votes
4answers
12k 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 ...
3
votes
1answer
585 views

SQL Globalization - DateTimeOffset

What is the recommended best practice when it comes to storing date/time for an application that needs to support multiple timezones? Which is best ? Use the SQL DateTime data type and insert ...
0
votes
1answer
152 views

What are the first necessary actions needed to help secure a database's schema & data? [closed]

This smells like an open-ended, opinionated, wiki free-for-all, but I'm not trying to make this a battle of opinions. It seems like there should be a good checklist out there for all to agree on ...
7
votes
5answers
983 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 ...
3
votes
1answer
863 views

Webapp & MySQL: Row Level Security

I am trying to emulate row level security on a webapp I am developing using MySQL. Using this method: Creating a database with the the required tables where the data pertaining to all the users will ...
2
votes
1answer
458 views

Decision tables in the database: a valid solution?

I have to extend a .Net 4/C# case management application that uses Oracle 11g to handle legal cases. I have minimal budget, my experience with Oracle and a very junior C# developer to accomplish ...
22
votes
4answers
9k 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 ...
1
vote
1answer
507 views

Storing Passport Numbers in a database

We're developing a travel/tourist application for a tours company who want to be able to input traveler information into an online database. Is there any security best practice for storing passport ...
1
vote
1answer
270 views

Design Tagging module in the database

I've a database system which we want to define tags for many tables into it. For example I've videos, photos, ... different entities that we wish to make them taggable. The tags could be anything. By ...
10
votes
4answers
4k 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 ...
2
votes
0answers
178 views

Stored procedures over SQL in the code, good idea? [duplicate]

Possible Duplicate: What are the arguments against or for putting application logic in the database layer? Assuming I got over the problem of maintainability and debugging. My assumption is ...
2
votes
2answers
524 views

What reason is there to create a physical table for a report output?

I've noticed that for Crystal Reports made by our organization and by some of our ERA software providers have a tendency to use physical tables for their reports' data sets, rather than using a view ...
6
votes
2answers
2k 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 ...
3
votes
2answers
1k 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? ...
8
votes
4answers
5k 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
817 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 ...
16
votes
4answers
1k 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
386 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
2k 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 ...
11
votes
3answers
5k views

What does a best-practice SQL Server maintenance plan look like? [closed]

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
2k 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
439 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
2k 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 ...
32
votes
6answers
20k 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
419 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
2answers
12k 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
1k 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
1k 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 ...
23
votes
4answers
59k 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
570 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, ...
6
votes
1answer
2k 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 ...
41
votes
12answers
5k 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 ...
8
votes
3answers
3k 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
334 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
510 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
1k 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 ...
4
votes
2answers
535 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
617 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 ...