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
0answers
255 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 ...
3
votes
3answers
2k 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
212 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 ...
2
votes
3answers
198 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
158 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 ...
0
votes
1answer
77 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 ...
2
votes
3answers
614 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
88 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
594 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 ...
0
votes
3answers
86 views

Is it good practice to create new tables for new elements in a database?

I am trying to create a good model for a database that I need for an application. The way that I have tought about realizing it involves creating a new table for each new element that is added, but I ...
3
votes
1answer
3k views

Max Memory settings on Multi-Instance SQL Server 2008 R2 Cluster

SQL Server 2008 R2 multi-instance cluster (on VMware vSphere 5.1) 2 nodes, each running 2 instances 16GB RAM per node. The only time the instances are on the same node is when I am patching the ...
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 ...
3
votes
1answer
2k views

MySQL: Alter Table on very active table gives “Waiting for table metadata lock”

At my work, we've recently upgraded to MySQL 5.5 and since then, have been bitten a few times by the new (and intended) behavior of metadata locking. Basically, whenever we run an ALTER TABLE ...
5
votes
2answers
1k views

Postgres DDL and DML best practices and coding standards [closed]

Is there a best practice or coding/design conventions for Postgres both DDL and DML/SQL that you could share and recommend. I am looking for something similar to what Google has for programming. ...
9
votes
2answers
8k views

What steps are necessary to safely restart a machine hosting Microsoft SQL Server?

Are there any special steps necessary to prevent data corruption when restarting a server hosting an MS SQL instance? For example, I recently encountered the recommendation of stopping the SQL ...
1
vote
1answer
74 views

Is it a good way to use different schemas for storing the data and provide data access?

For a new product we came along with a pattern, where all the data tables are stored in one schema (lets call it storage), while the clients connect to another one (here called api). The api schema ...
2
votes
2answers
10k views

Splitting a large table to improve performance

This is a follow-up to an earlier question. I have a SQL Server 2008 R2 Standard server, that holds a single database, which itself has almost nothing except a large table. The table is 100+ million ...
3
votes
3answers
347 views

Why set up static data in views vs. using tables in mysql?

I get an LDAP feed nightly. I get it as a text file and dump/create my LDAPALL table. There are roughly 75K employees times about 50 fields. I have the following too: LDAPIMPORTANT - view that ...
1
vote
2answers
233 views

How to avoid timeouts during big DML operations

I have a big insert script I need to run. Its about 55,000 records and 160 columns. The script is already created and I can't create it again. The problem I have is that this runs for about 4 hours ...
0
votes
1answer
580 views

How to revoke DBADM and clean up any related objects from DB2 LUW

This question has started because of us taking copies of production backups and restoring them into lower environments (with scrambled data of course) for developers to practice and/or debug against. ...
2
votes
2answers
1k views

Multiple Similar Tables vs One Master Table

I'm a web app developer and less experienced in database design, so would appreciate anyone's input. When I first built this web app, the app managed one type of object (table) with a handful of ...
0
votes
1answer
2k views

DB2 LUW: Should temporary tablespaces share bufferpools with other tablespaces?

We are in the process of tuning a massive application our company has just written. Part of that tuning includes tuning the databases. We run DB2 LUW on AIX. Specifically we are at 9.7 Fix Pack 4 ...
1
vote
1answer
108 views

When importing raw files for internal conversion, should I use a secondary database or just isolate them within the database?

When importing raw files for internal conversion, should I use a secondary database or just isolate them within the database? I've got between 1.25 and 2GB of CSV files to be imported (and already ...
0
votes
1answer
180 views

Relational database primary key requirements

I am trying to put together an ER Diagram to design a fairly simple asset tag tracking/inventory management system for our servers/switches/UPS/etc. The top-level entity corresponds to the business ...
0
votes
2answers
321 views

Should numeric identifiers like serial numbers be stored as integers?

Many of our databases store numeric tracking codes (like serial numbers) as integers. There is little chance of these codes every getting alphabetic characters, but it still seems like an incorrect ...
26
votes
2answers
14k views

Is it a bad practice to always create a transaction?

Is it a bad practice to always create a transaction? For example, it is a good practice to create a transaction for nothing but one simple SELECT? What is the cost of creating a transaction when it ...
6
votes
3answers
515 views

Is using SUM() twice suboptimal?

I know I have to write SUM twice, if I wish to use it in a HAVING clause (or use a derived table otherwise): SELECT id, sum(hours) AS totalhours FROM mytable GROUP BY id HAVING sum(hours) ...
0
votes
3answers
2k views

Is it a good practice to create tables dynamically in a site?

A friend asked me to build a site with a few "static" and "dynamic" tables. In fact he wants to have a few tables which can't be deleted, and some "dynamic" tables which can be created directly from ...
5
votes
1answer
1k views

What is the safest way to run SSIS on Multiple Named SQL Server Instances?

I have an Active/Active SQL Server 2008 R2 cluster. There are two named instances on each node and no default instance. I need to deploy SSIS packages to the different instances. To do this, I ...
2
votes
1answer
233 views

Need help to design model with multiple rights

For a website, I have multiple elements that need to be moderated by many different users. Until now, I have always used Role-Based Access Control (RBAC) model but I want a more flexible model. I ...
3
votes
3answers
173 views

pros/cons of different ways to store whether a record is one of two options?

I am trying to store whether an address is a Work address or a Home address. There will never be another type of address. I'm wondering what the pros/cons are of the different ways to store this, ...
4
votes
2answers
1k views

maintaining full text index on large table

I'm on SQL Server 2008 and have a table, for reporting purposes, with 500,000 records that will easily reach the millions. The table will employ a full text index for rapid searching on a handful of ...
2
votes
1answer
814 views

What are the hazards of upgrading a SQL Server service pack?

Are there any specific pitfalls/standard considerations when upgrading an instance of MS SQL Server to a higher service pack? Is there anything special that is generally done besides making sure there ...
1
vote
1answer
196 views

Database design: nested tagging

I have a situation that after an initial thought ended in the following tables: section: id, name section_tag: id, name section_tag_map: section_id, tag_id item: id, name ...
1
vote
2answers
369 views

Foreign Key constraint on fixed value field - Ever appropriate?

I have a short (15 rows) look-up table that lists the valid values for several columns in the database. I don't think it can be considered a One-True-Lookup-Table, it is about a single definite ...
3
votes
0answers
194 views

Replicating databases for local and cloud instances

Forgive me if this is an inappropriate place for this architectural question but I'm not sure where it best fits in the StackExchange family. I have been tasked with developing an online/live auction ...
5
votes
2answers
593 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 ...
0
votes
2answers
93 views

Adding a bool column; tracking untouched vs. false

[backstory] I have a simple database of our current Widget inventory. It averages just dozen columns across five or six tables, but has a huge number of records already. Some Widgets came with ...
1
vote
2answers
336 views

Extensible Asset Database Schema

Background I work for a decent sized company in the hardware sector. Currently, we have a number of varying assets that need to be tracked. Until recently, this was done by hand as was an ...
1
vote
1answer
292 views

table design to track significant events associated with an item

An Orders table I'm dealing with at present in design phase has about 10 or more columns. Each OrderID in the Orders table will have certain significant events or milestones. The number of Orders ...
8
votes
4answers
1k 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 ...
0
votes
1answer
239 views

structure when storing books in elasticsearch

I want to use elasticsearch as a web search engine for books. A book has several editions with different titles in different languages, ISBN's as well as author names in different languages. I want ...
7
votes
2answers
1k 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 ...
3
votes
1answer
341 views

Microsoft Baseline Configuration Analyser on Production

I came across an useful tool that Microsoft has released for configuration analysis. It's called Microsoft Baseline Configuration Analyser 2.0. I downloaded and ran it on my local instance and got a ...
0
votes
2answers
110 views

How to design data about a column can belong to the whole table?

I'm not a DBA so I apologize for lack of knowledge. Here's my situation...Say I have several tables. Products, Customers, Vendors, etc. Our application may have a help text for some of the data in ...
24
votes
3answers
57k 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 ...
1
vote
5answers
10k views

Coding an accounting database from scratch? [closed]

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 ...
3
votes
1answer
844 views

Payment methods conceptual and logical model

I need to create a conceptual and logical (normalized) models of parking house according to the requirements below. It looks to me as a very simple concept that doesn't need all tables to have ...
4
votes
3answers
730 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 ...
2
votes
3answers
14k views

which mysql version to use - 5.1 or 5.5?

I am designing a new database. There are going to be around 1000 write queries per second. There may be as much as 10000 read per second. Which MySQL version is recommended? I came across a post on ...