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
1answer
33 views

Best way to create dynamic table for search

There is a table of documents: CREATE TABLE `docs` ( `doc_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `doc_uuid` binary(16) NOT NULL, `sender_uuid` varchar(40) NOT NULL, `receiver_uuid` ...
2
votes
1answer
45 views

Creating a helper UDF or stored procedure and limiting external access

I'm looking for a recommended best practice for dividing functions and stored procedures into a 'public API' and 'private implementation details'. I'm working on a security system and I want client ...
0
votes
0answers
15 views

OLTP to OLAP process causing deadlocks and impacting customers

I should preface this with the fact that I'm a .net developer who has fallen into DBA and performance work because it has become business critical, so I'm missing the DBA skills for this kind of work. ...
0
votes
0answers
19 views

Is VSS Backup a better solution than the native backup?

Since i started to work as a DBA, i'm making normal backups ( with scripts, ad hoc and etc ). What would be the better solution for a backup? lets say I need to migrate a 2tb database to development ...
3
votes
2answers
63 views

What are the best practises for using Code Tables in Relational Database?

Code Tables are usually a collection of possible values that can be given for a column and it is stored as a Table. For example, suppose we have a Code Table party_code, the possible values are ...
1
vote
1answer
29 views

Mongodb performance: compound query vs moving to other collection

My application uses mongodb to store order information. At some point orders are sent after which I need to generate some reporting of all orders in a timerange. To give an idea of my order document: ...
1
vote
0answers
27 views

Key/value table: what is a good way to organize it if value could be foreign key?

Suppose a have a structure of two tables Table Key names: |_Key name_|_Parent_key_id_| | key1 | NULL | | key2 | key1 | Table Key values: |_Key__|_Value__|_Version__| | ...
6
votes
2answers
99 views

SQL perf issue - configure server correctly first or troubleshoot issues one by one?

This is for SQL Server 2012. I am troubleshooting overall slow performance on a Dynamics AX environment. Everything is just slow - app itself, reports, ad-hoc, etc. Right off the bat, I can see ...
2
votes
0answers
15 views

What is the best way to keep track of 1000 RDS hosted databases?

I want to know what is 'best-practices' for managing an extreme number of RDS instances. I have no problem managing five. But managing dozens or hundreds, that's another task. I'm thinking use a ...
1
vote
2answers
47 views

How to Apply a Title-Case RegEx Condition for Object Names with Policy Based Management

I'm implementing PBM on a SQL Server 2014 instance and want to enforce a condition that all objects are created outside the dbo schema and all object names are created in title-case (i.e., OrderDetail)...
3
votes
1answer
60 views

Is EXECUTE AS OWNER in msdb a security risk?

I've been tasked with creating a system that allows non-privileged users to start and stop SQL Server Agent jobs. I'm creating stored procedures in the dbo schema in the msdb database using the WITH ...
3
votes
1answer
41 views

Store the content book in database, so that it would be updated afterwards or store them as document?

I have some books in (urdu) made with Urdu Inpage(tool for making pages in urdu) like this: problem that these document files are difficult to read in php because they gives unread-able characters, ...
6
votes
1answer
176 views

Principle of least privilege for sql server agent jobs

I've just read this article which advocates using the sa account for ownership of sql server agent jobs. http://sqlmag.com/blog/sql-server-tip-assign-ownership-jobs-sysadmin-account He has a couple ...
21
votes
8answers
2k views

What problems are solved by splitting street addresses into individual columns?

We have a team who designs the tables and relations for software developers. In our organization, they are pretty strict about enforcing 3NF normalization - which to be honest, I agree with given the ...
0
votes
0answers
57 views

Comparisons with NULL values and not using ISNULL function

Hello and thank you in advance. My question is that with ansi-nulls on, how can I do a proper comparison between two fields without using an isnull function (or coalesce)? For example the following ...
1
vote
1answer
38 views

Login Schema Role User Setup

Environment: Windows Server 2012 SQL Server 2014 Standard Public website (Site-A) on Server Private website (Site-B) on Server Windows App running on workstations SQL Server 2014 Standard: 2 ...
18
votes
1answer
785 views

Setting BUFFERCOUNT, BLOCKSIZE, and MAXTRANSFERSIZE for BACKUP command

I am looking for practical guidance for setting the values for the BUFFERCOUNT, BLOCKSIZE, and MAXTRANSFERSIZE of the BACKUP command. I have done a bit of research (see below), I have done a bit of ...
14
votes
3answers
171 views

Transaction Log Backups Serial or Parallel?

We happen to be using SQL Server 2012 Standard Edition. I also happen to use Ola Hallengren's scripts to provide an easy, more flexible framework for doing backups and maintenance. This question isn'...
4
votes
2answers
65 views

MySQL: Soft Delete post likes

I have implemented a like/unlike features for posts in one of my projects. The like table is normalized and has a column visible. The schema is as follows: Column Type Null Default ...
0
votes
0answers
15 views

Relationship based access control (ReBAC)

In my application, access control is based on relationships between entities. Consider this simplified schema: CREATE TABLE user (...); CREATE TABLE group (...); CREATE TABLE group_members( ...
1
vote
1answer
455 views

Is introducing a where condition into the join clause bad practice

In the query below, the condition and r.master_seq=206 is used on the on clause as a join condition. In the programme code, the value for r.master_seq is set by a parameter. My concern is ...
4
votes
1answer
230 views

Is splitting a CSV column into a separate table(enforcing 1nf) unnecessary complication?

I had very carefully asked the table designer why he had chosen to create CSV columns in multiple tables. The designer's answer was that splitting CSV columns into separate tables was unnecessarily ...
1
vote
1answer
46 views

Chance of duplicate on single select max into statement

Against my will, I am told to use the following method to generate a new pk value. INSERT INTO SERVER_MONITERING(SM_SEQ, CPU_USE, MEMORY_BASE, MEMORY_USE, DISK_BASE, DISK_USE, SERVER_IP,...
0
votes
0answers
197 views

A “best practice” Postgresql function template for ETL processes

I use SQL Server in my day job, and have a personal project to build a star schema data warehouse in Postgres 9.4 on Ubuntu 14.04 to extend my knowledge (it'll be approximately 300GB uncompressed). I'...
0
votes
2answers
83 views

Select max(pk) instead of sequence.nextval

I have a client/superior who keeps suggesting that I select max(primary_key) when I insert a new row. I gut says it's VERY wrong but I don't have exact reasons to support my suggestion. The only ...
0
votes
1answer
38 views

Consultants using old version of Hibernate ORM for new application - should I be concerned?

My company currently has consultants developing a Java-based application. They are using Hibernate ORM 3.5 (released 5 1/2 years ago in 2010) in this application. The back-end is SQL Server 2012. ...
0
votes
0answers
46 views

How much normalization is too much

I am working on designing a database for a site I am trying to build, I created what I believe to be the best normalization strategy, to limit the size of particular tables, and the number of tables ...
0
votes
1answer
30 views

Upgrading a big database inside a transaction

Assuming a big database, where normal upgrade (structural changes and data upgrade) currently takes about 2-3 days (not a issue, is not done during production and about 1-2 times per year. Currently, ...
1
vote
1answer
51 views

Is it recommend to create a table for addresses? [closed]

I have a table persons with the columns per_id, per_passport, per_name and per_address. Two or more persons can live in the same address (like families or room-mates) so if this happened, the data is ...
20
votes
3answers
1k views

When is it better to create STATISTICS instead of creating an Index?

I have found plenty of information on what STATISTICS are: how they are maintained, how they can be created manually or automatically from queries or indexes, and so on. But, I have been unable to ...
1
vote
1answer
92 views

Should child comment be treated as separate table?

I'm building a commenting system, currently my table indicates which comment is child with parent id in 1 table, I would like to know what is the best way of doing this? Current Table Scheme (1 Table)...
6
votes
1answer
68 views

How to encode a known date but unknown year?

I am using a Postgres database, and have a table with a date-time field. For many records, we know the year but not the date. Sometimes we know the date but not the year. And other times we know the ...
2
votes
3answers
241 views

Table naming and Database normalization

I'm creating a database of simulation results, and am trying to do so the right way. I've attempted to show relationships between data to make sure nothing redundant is thrown in. My current ...
2
votes
1answer
163 views

Best Practice for Passing and Working With Parameters Security/Performance

Our senior programmer has been having me write stored procedures in the following format to protect against injection attacks. He says that the best practice is to take the parameter in, then declare ...
1
vote
0answers
68 views

Is not having 'named' timezone support common in MySQL?

I'm trying to set the timezone the database uses using: "SET time_zone = 'UTC'; Whilst this works on my development server it doesn't work on my staging server; I get this error: General error: ...
0
votes
0answers
137 views

User is An employee. Role or relation

I've created a database based on a party-model. In short I show you the tables: parties, kind, party_to_party_relations, role, party_roles table parties: CREATE TABLE `parties` ( `id` int(11) NOT ...
5
votes
2answers
1k views

Storing a Billing Address Best Practice in Orders Table

Can someone help me understand this user's answer for a CustomerLocation table. I really want a good method for storing addresses in the orders table. What I'm looking for is how I can set up my ...
1
vote
1answer
220 views

MySQL: is it more efficient to query for duplicate row or insert(update) on duplicate?

Just curious on resource efficiency. If I have a list of inserts that need to be accomplished repeatedly which may contain duplicates (there is no way around this, the API I have to work with sucks), ...
2
votes
2answers
944 views

Is it recommended to install extensions into pg_catalog schema?

Since objects in pg_catalog schema are implicitly in the search_path (docs), would it be recommended to install extensions in that schema?
3
votes
1answer
228 views

What is the proper setup for PG user for web application

We have a web app that detects if the DB does not exist and creates it. Users like it, but my thought is that such user has too much privileges. I would optimally propose to change the DB creation ...
1
vote
1answer
51 views

Should database directory and file access be restricted to only the db user?

I don't know exactly how to word this question and I haven't been able to find any specifics on it so apologies if it's a duplicate. I know that it's standard practice to have database server ...
3
votes
1answer
56 views

Should I create a extra relation-table when I have two columns in one table referencing to one column in another table

My problem is best explained by example: Suppose I have a database about chess games and players. Which one of the following structures should I choose Option 1) player (player_id,name... etc) ...
1
vote
1answer
1k 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
1answer
43 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
84 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. ...
1
vote
0answers
31 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
155 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
51 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
68 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 ...
1
vote
2answers
2k 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 ...