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

1
vote
5answers
6k views

Coding an accounting database from scratch?

My new medical practice is picking up fast and my homemade Libreoffice Calc spreadsheet is quickly becoming cumbersome to track patient visits, accounts receivable, overhead reconciliation, etc. Most ...
2
votes
0answers
16 views

Schema for user drag and drop ordering

I'm trying to work out the schema for a system where users will be able to order items in an (unlimited length) list by drag and drop. Is there any kind of standard way of storing this ordering ...
0
votes
0answers
27 views

SQL Server Database Table Partitioning Consideration

I have a SQL Server table with 42 million + records. This table acts as a staging area for transaction records loaded from a different system. Data from this staging table have to be transformed and ...
0
votes
1answer
29 views

Indicators for right balance of the number of DB2 instances per AIX LPAR

I have a question that has been bugging me, but I just don't know how to validate it. We run DB2 LUW on AIX in our shop. Our AIX is currently at 7.1. Our DB2 is at 9.7 FP 4 for most installations and ...
2
votes
1answer
62 views

Inheritance or lot of relationship?

I have to transpose a class Diagram into a Physical Data Model (more precisely MySQL database structure). In my class diagram, I have a lot of entities (A, B, C, D) which inherit from an other ...
2
votes
1answer
727 views

Data archival strategy that handles schema changes?

I am working with a legacy application that has about ten years of customer data. A majority of this data is not used in day-to-day operations but there is a business requirement to have the data ...
0
votes
1answer
93 views

No-SQL Database Design for restaurant app

I've been working with SQL databases all my life and I am now trying to get into the NoSQL world. I am a looking for a piece of advice on whats the best practice in order to achieve this. Suppose I ...
17
votes
4answers
45k 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 ...
19
votes
4answers
7k 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
50 views

Tables names in a supertype/subtype schema

I have 3 types of users in my app. The users share some common attributes, but they also have quite a bit of stuff that pertains uniquely to each user type. I have a person table that contains limited ...
1
vote
1answer
97 views

Oracle - WHY do an ORDER BY for INSERTs AS SELECT?

I have an application that was migrated from MSSQL to Oracle 11G. It needs re-design but that's outside the scope of the project. In short, the application gets legacy data, builds a parent table ...
82
votes
18answers
17k 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 ...
5
votes
4answers
200 views

How do I tactfully bring up database shortcomings? [closed]

A little background: I recently inherited responsibility for support and development of a database application after the original developers left. Looking through the application, the database ...
1
vote
2answers
62 views

Merits of inserting to temp table and renaming versus just adding/removing columns

If I add or drop columns from a SQL Server table I presume I get page splits or gaps. Since the size of the row has changed. When I use RedGate SQL Compare to create conversion scripts its strategy ...
0
votes
2answers
107 views

Does anyone use modules in DB2 LUW?

Our environment is currently DB2 9.7 Fix Pack 4 on AIX. I am in the processing of designing and coding some SQL PL stored procedures responsible for archiving data into an archive schema and then ...
7
votes
3answers
23k views

Granting access to all tables for a user

I'm a new to Postgres and trying to migrate our MySQL databases over. In MySQL I can grant SELECT, UPDATE, INSERT, DELETE privileges on a low privileged user and enable those grants to apply to all ...
0
votes
1answer
58 views

creating a 24 hours data table - Suggestions/improvements

I am planing to have a table to store the data based on every hour , in short I have a file with 24 hour data and I need to use this to populate a hourly bar/line graph. Below is a sample 24hr data ...
0
votes
1answer
2k views

DB2 9.7 LUW - How do I SET SESSION_USER when I am DBADM,SECADM?

Based on a previous question I have asked, I am attempting to work on a solution as recommended by mustaccio's answer, whereby I have another ID to create my DDL objects. I have been trying to follow ...
1
vote
2answers
198 views

Best practices for service name / aliases in tnsnames

I have this idea that service names / aliases given to connections descritors in a tnsnames.ora (not the one in the server that dblinks use, but the one for the public) file should in no way be ...
1
vote
1answer
607 views

Efficient way to insert/update/delete table records from complex query in Postgres 9.x

I have this function which returns a set a records and I need to persist those records into a table. I have to do it hundred times a day. My initial approach was just clear data from my table and ...
3
votes
5answers
585 views

Using Timestamp plus Integer as Primary Key

In Sql Server, what would the drawbacks and disadvantages be of using a primary key that is: Two Character Table Identifier + YEAR + MONTH + DAY + HOUR + MINUTE + MILLISECOND + random integer between ...
0
votes
1answer
119 views

Inheritance discriminator column better int or varchar or enum?

What is the best practice for storing a discriminator column ? Does it make any difference ? Any pros/cons of using one over the other ? I am using MySql with Entity Framework but the question goes ...
1
vote
0answers
70 views

Where Can I find *Current* PostgreSQL advice for a SQL Server DBA? [closed]

Been a MSSQL DBA for about 7 years. I have the fundamentals of performance, backups, replication, security and design down for MSSQL. I'm not setting up new applications using PostgreSQL 9.2. I have ...
1
vote
1answer
46 views

Organizing Database Objects in a Database that are not part of the systems original Database Objects?

I've been working at a place for about 2 years now with a series of heterogeneous legacy database applications, which are tied together using 3rd party Microsoft Access databases (*.mdb). Soon we ...
2
votes
2answers
50 views

Should I opt for a new database or adding more tables?

this is a question around best practice that I'm currently dealing with. I have a database (SQL Server 2008 R2) with a heap of tables (around 40) with data, now the need has risen to have logging of ...
0
votes
2answers
469 views

When should I NOT set the Maximum Server Memory option in SQL Server?

In SQL Server, I normally set the Maximum Server Memory setting to one of the popular best practices, such as leaving 10% or 4GB, whichever is higher. ...
0
votes
2answers
103 views

Are #tables and #procedures 'not best practice' or 'bad practice'?

I am working on a project and part of it is an interface tool that executes SQL code stored in a xml file to import/export files. In the file, temp procedures and temp tables are used quite heavily ...
39
votes
5answers
13k 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 ...
0
votes
0answers
26 views

Lock rows for long running transactions

I have a question table from which agents try to answer 5 questions at a time. Obviously, I want to avoid agent collision i.e. if an agent is answering a question, other agents can not work on it. Is ...
0
votes
3answers
55 views

Item availability in row or another table

I'm a database newbie, so please bear with me. Say I'm creating a library database. I have a table containing information about books. Do you put the book availability as a row in this table or would ...
1
vote
1answer
43 views

UK Addresses in 3NF

I am in the process of creating a UK only database which holds information for various sets of people i.e. Staff, Clients etc. The tables for each of these sets of people have an Address, City and ...
0
votes
2answers
170 views

Memory Allocation in Shared DB & IIS Environment

In my use case, we have production servers that host both IIS & SQL on the same machine, not ideal but no flexibility to change that. Basically a single machine is both a DB and an Application ...
1
vote
1answer
148 views

What's better here - a single column or multicolumn primary key?

Let's assume you have a table groups and a table item. Each item belongs to exactly one group. It is an inherent part of that group. An item cannot exist outside of a group and it cannot be moved to ...
0
votes
0answers
27 views

Hints about Datatypes

I'm currently writing some style guides for a new project where we want to use Oracle for the first time in a extensive manor. I already wrote down some naming conventions for tables, columns, pk, ... ...
12
votes
1answer
3k views

GO After every T-SQL statement

What is the reasoning behind using the GO statement after every SQL statement? I understand that GO signals the end of batch and/or allows the reputation of statements but what advantage does it have ...
0
votes
0answers
22 views

Performance One Row With Total Vs Multiple Rows with Seperate Values?

Not 100% sure how to explain this situation but here it goes: I am building an Inventory Database that all items received are entered into and a label is created for each Container, that is going to ...
0
votes
1answer
139 views

Best practices in securing Oracle Standard edition one [closed]

I have a database server with Oracle Standard Edition One. I'm interested what are the best practices to secure Oracle? I have Java web application installed in the same server. The Java application ...
0
votes
1answer
51 views

Query optimization ndbcluster + order by

I'm not really all too familiar with the ndbcluster storage engine of MySQL, but I've recently written an application that does some very simple queries. However, a co-worker of mine (who is now on ...
1
vote
0answers
166 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 ...
38
votes
12answers
4k 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 ...
10
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
3answers
1k views

Best practices for configuring the Drive/Filegroup setup for Microsoft SQL 2008

I am a software developer for a small company. I wish we had a dba but not in the cards at this point for this company. I was working on migrating our 75gb SQL Server 2000 database to SQL Server 2008 ...
2
votes
0answers
150 views

Use Matlab or SQL Server to do mathematical computations? [closed]

Current situtation I am working with a dozen of CSV files containing financial simulations in form of 100'000x100 matrices. Each file is around 100Mb large. Until now, only analyse these files with ...
3
votes
2answers
4k views

changing float->numeric casts from assignment to implicit, dangerous?

In porting an application to PostgreSQL (9.1), one odd SQL incompatibility I've discovered concerns the round() function, specifically the version that takes a second argument indicating the rounding ...
29
votes
5answers
13k 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....
2
votes
3answers
149 views

How to make stored procedures more readable without harming performance

I come from OOP background, and I need to write and maintain huge stored procedures. Naturally, the sprocs become quite unreadable very quickly, no matter how much I try formatting, giving readable ...
1
vote
1answer
108 views

MySQL DB dynamic ranking or “static” separately saved

I have a table where I store the points with the connected categories and game-types of each user. There are different ways to get points. However everytime when the points need to get displayed in a ...
2
votes
3answers
370 views

Uniqueidentifier vs. IDENTITY vs. Material Code --which is the best choice for primary key? [closed]

Which one is the best choice for primary key in SQL Server?There are some example code: Uniqueidentifiers e.g. CREATE TABLE new_employees (employeeId UNIQUEIDENTIFIER DEFAULT NEWID(), ...
2
votes
2answers
82 views

Is it worth it to create an “Id” column?

I have a table which has a column Name, which is of type VARCHAR2(20), unique, cannot be null, and cannot be changed. At first I used it as a Primary Key for this table but I was wondering if it was ...
1
vote
3answers
386 views

Proper use of database functions for business logic

I have a database in postgres with 147 tables; these tables contain data generated by functions that have most of the logic of the system, for example how to build an accounting entry. These ...