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

0
votes
1answer
503 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. ...
32
votes
6answers
17k 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....
1
vote
1answer
27 views

(MySQL) user table and profile separate, or same table?

I'm creating a blogging site that anyone can sign up and be an author for. I want each author to have their own public profile. My main question is; would it be better practice to have a separate ...
1
vote
0answers
50 views

SQL Server 2012 Security Best Practices [closed]

I'm looking to compile a good list of SQL Server 2012 security best practices. Do you know of any good articles or lists?
18
votes
3answers
42k 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 ...
2
votes
2answers
9k 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 ...
1
vote
1answer
28 views

Need advice on Primary and Clustering Keys of a table

I need advise on a table which is part of a development project I'm involved. A brief summary on the use of the table: Data from multiple databases will be consolidated in this table for the purpose ...
0
votes
1answer
37 views

How should I store changes to items in the database?

I have items (tracked with itemid) that have attributes assigned to them (field1,field1 for example). At any given time, field1=val1, field2=val1, ..., fieldn=valn where val1,val1,...,valn = 1..100. ...
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
0answers
26 views

How do I abstract a WHERE clause condition?

I'm wondering how a condition in a commonly used WHERE clause can be abstracted away so that it can be safely changed. Say I have data like ColA | ColB | ColC ================== 1 | 2 | ABC ...
1
vote
1answer
57 views

TempDB Best Practices For Shared Storage [closed]

Microsoft recommends separating the SQL Data Files from the Temp Files to limit device contention but what about cases involving shared storage. I have a shared environment where the data and temp ...
1
vote
1answer
38 views

Best place to keep backup scripts

Where is the best place to leave the Database Backup scripts, currently I let them on the Backup Job itself, however I wonder if it's best to leave on a Database
-4
votes
1answer
52 views

Create database unique Surrogate Keys without using GUID’s

What is the best way to get database unique surrogate keys in Microsoft SQL Server when using something like “auto increment” as opposed to GUID's. The purpose is to increase performance over using ...
0
votes
2answers
168 views

MongoDB: co-locate the mongos process on application servers

I would like to ask a question about a best practice described in this document: http://info.mongodb.com/rs/mongodb/images/MongoDB-Performance-Best-Practices.pdf Use multiple query routers. Use ...
21
votes
4answers
54k 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 ...
0
votes
1answer
57 views

Normalising Customers and Employers with a people table

At what normalisation stage is it recommend to split say Customers and Employees into a people table and then link to the unique attributes of Customers and Employees. I cant seem to find the ...
0
votes
1answer
34 views

Grant access to all tables of a database

I recently wanted to share regular access rights with one user of a server and I realized that a simple CREATE USER and GRANT ALL ON DATABASE commands didn't let him run a simple SELECT on the data. ...
1
vote
1answer
56 views

Which of these structures are good for maintaining history data?

Iam creating an application which is being used to send reminders. Here is the requirements. We have set of Students. Every student has a Student Pass, this need to be renewed in every month. ...
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 ...
1
vote
0answers
46 views

Is it bad practice to launch more than 100 sql statements at once?

I have PHP code that is meant to updating the billing status of someone. I only want it to update if the bill has been successfully created and therefore I think a possible solution would be to ...
3
votes
2answers
192 views

How to properly increment an int identity in SQL Server if the Identity Increment isn't set?

When I use SQL Server with an int based identity column, I always use the auto increment. But I'm needing to insert into an existing table that doesn't have the property set, and I'm wondering what ...
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
27 views

Using part of a compound key as foreign key

We have the following situation, in which we have three tables: Article ------- - Id (PK) - Supplier_Id - Supplier_Contactperson_Id - Name Supplier --------- - Id (PK) - Name SupplierContactperson ...
15
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 ...
6
votes
4answers
2k views

Datawarehouse bad practice?

In our Datawarehouse context, we have to update some record in fact tables inside our ETL. One thing we did is to create nonclustered indexes just before the heaviest queries, and drop them ...
1
vote
1answer
49 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 ...
1
vote
1answer
84 views

What's the best practice to avoid collisions in throwaway temp tables?

In SQL Server, I'm used to creating a #temp table for temporary storage in a complex stored procedure. Not wanting to clutter up the system, I DROP the table when done. However, if I do the same in ...
6
votes
3answers
404 views

Best practices for change management with indexes

Our IT shop is first starting to build a group of DBA's. All of us (myself included) have come over from the application development/architecture world, so the DBA world is still fairly new to us. ...
1
vote
5answers
9k 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 ...
2
votes
0answers
53 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
1answer
59 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
103 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
947 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
1answer
163 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 ...
1
vote
1answer
80 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
206 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 ...
95
votes
18answers
20k 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
202 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
157 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
220 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 ...
0
votes
1answer
75 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
870 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
3k 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
1k 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
179 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
1answer
66 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
51 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
3k 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
137 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 ...