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

16
votes
1answer
110 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 ...
4
votes
2answers
45 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 ...
3
votes
2answers
35 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
11 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
432 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
171 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
33 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, ...
0
votes
0answers
54 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). ...
0
votes
2answers
69 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
36 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
43 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
40 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 ...
19
votes
3answers
459 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
76 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 ...
6
votes
1answer
48 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
135 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
75 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
59 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
77 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
450 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
110 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
364 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?
2
votes
1answer
103 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
42 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
54 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
671 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
80 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?
1
vote
1answer
39 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
62 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
116 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
47 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
67 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 ...
2
votes
2answers
163 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 ...
1
vote
2answers
967 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 ...
0
votes
1answer
607 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
164 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. ...
1
vote
0answers
55 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
2k 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 ...
0
votes
1answer
31 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 ...
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
128 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 ...
2
votes
0answers
94 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 ...
1
vote
1answer
73 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
103 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 ...
1
vote
1answer
294 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 ...
2
votes
2answers
130 views

Tables names in a supertype/subtype schema [closed]

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
500 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 ...
5
votes
4answers
210 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 ...