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

6
votes
3answers
281 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
779 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 ...
4
votes
1answer
530 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
167 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
144 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
944 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 ...
1
vote
1answer
447 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
134 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
236 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
110 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
395 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
90 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
220 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
171 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
663 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
190 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 ...
6
votes
2answers
690 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 ...
2
votes
1answer
248 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
103 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 ...
5
votes
3answers
18k 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
5k 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
1answer
577 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
611 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 ...
1
vote
3answers
10k 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 ...
0
votes
1answer
334 views

SQL Server Job logging best practice

To be able to design a robust Job in SQL Server, we need to have log for each step of the job. I know there are different options available which help us for troubleshooting a job, but what if we want ...
0
votes
1answer
299 views

Design database to query a date range for scheduling

I am designing a db that should include a table with fields for datetime scheduling, so as I know what kind of queries will be made​​ periodically I would like to design the table for them to be as ...
2
votes
1answer
940 views

Recompiling dependent objects in Oracle

I wonder what is the best practice to recompile Oracle 10g programmable objects that become invalid due to changes in one of them. To be more specific, I have a few packages, that use schema level ...
3
votes
3answers
1k views

Best practice for a DBA to login to a database

I've read that a DBA never should login to a database as system user. That makes me wondering... how should a DBA login to a database? Should he/she create a DBA account and use it for login purposes? ...
3
votes
1answer
48 views

MySQL Help with Desktop Based Administration

I am making a MySQL based database for a professor and she would like to have a version uploaded to the web and a version on her desktop for personal use. The web version would be "official" whereas ...
5
votes
3answers
443 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 ...
4
votes
2answers
424 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 ...
1
vote
1answer
632 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 ...
1
vote
3answers
121 views

Storing summed values

I'm trying to create a database to store values for Profit & Loss statements and I'm wondering what the best way to structure this would be. Specifically, amounts that are are equated from other ...
3
votes
1answer
629 views

Robust Mysql failover using free tools

I am looking for a free solution to set up Mysql in an active/passive configuration, in order to provide high-availability. In particular, a Bugzilla installation, and MediaWiki needs to stay alive ...
1
vote
2answers
188 views

For a boolean settings table, should I toggle a flag, or insert/delete?

I have a settings table. SETTINGS -------- settingId int settingName varchar(20) Each user can choose to apply the settings, or not. The interface allows them to quickly toggle the settings on ...
3
votes
2answers
198 views

Store categories in database or use hard coded integers?

An address can belong to either the mailing or billing categories. When a user adds an address to a table, should the address category be an independent integer value set by a constant somewhere in ...
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" ...
1
vote
2answers
707 views

Database design experienced view

I want to make a database that stores countries, their respective states, and cities. I am confused how to normalize these. Can anyone help? My ideas: Table 1 Countries (id pk, name , code) ...
3
votes
2answers
301 views

Database modelling best practice question

I am modelling a database for an application I am developing and I am in front of a choice for which I would like to know what the best practice is. Let's say I have a Documents table and a Users ...
4
votes
4answers
4k 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 ...
2
votes
3answers
301 views

designing multiple tables while eliminating duplication?

As I'm working on an educational website, I'm facing a problem with my database design scheme - I can't find the correct design that eliminates duplication in the tables I'm creating. What I'm trying ...
2
votes
0answers
105 views

Are there any best practices before moving SQL Server to another node (controlled failover)?

Let's say there are multiple jobs running on the SQL Server - we have index maintenance jobs, backups, replication, etc. We would like to perform a switchover using Failover Cluster Manager. Are there ...
21
votes
4answers
6k 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 ...
3
votes
2answers
3k 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 ...
1
vote
1answer
206 views

Should indexes automatically be placed into their own bufferpool?

We work with DB2 LUW (specifically we are currently on 9.7 FP4) running on AIX. Per best practices from IBM, they recommend that when you build a table, you place the data, the indexes, and LOBs/LONG ...
3
votes
1answer
518 views

What is the best practice for physical deployment of data marts?

We are just starting to get into business intelligence at our company. We have some architects who are designing our data warehouse and also our data marts. They are using star schema for both ...
6
votes
2answers
560 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 ...
2
votes
2answers
271 views

Attacks on Postgresql listening for requests on 'localhost'

I am using jboss server for my servlets and the postgresql as the database server, listening on port 5432 on localhost. My jboss server is bound to a public IP. I want to know that since my postgesql ...
6
votes
1answer
1k 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 ...
1
vote
1answer
75 views

Configuration Persistance Design

We have a well designed and normalized DB, but, there is a requisit that could cloud this design. This image shows the design of part of the DB. The main functionality of our application is to create ...