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.
1
vote
1answer
153 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 ...
0
votes
2answers
42 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 ...
3
votes
1answer
45 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 ...
1
vote
0answers
25 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
67 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, ...
1
vote
0answers
93 views
Enforce a hard limit on write execution time during Amazon RDS write stalls
I am trying to recover from the situation where Amazon RDS write operations stall (some discussion on why this is here). If I show full processlist, I will see something like this:
318263 myapp1 ...
3
votes
2answers
50 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
46 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
37 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
...
10
votes
3answers
2k 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
2answers
65 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 ...
5
votes
2answers
160 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 ...
16
votes
3answers
4k views
When should I use a unique constraint instead of a unique index?
When I want a column to have distinct values, I can either use a constraint
create table t1(
id int primary key,
code varchar(10) unique NULL
);
go
or I can use a unique index
create table t2(
id ...
2
votes
0answers
36 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 ...
0
votes
2answers
54 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
69 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
72 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 ...
5
votes
1answer
404 views
Stored Procedures Best Practices: Fenced or Unfenced?
I believe I understand the reasons behind fenced and unfenced stored procedures.
Fenced run "outside" of the database (in our case DB2) so as to prevent possible corruption of the database engine ...
0
votes
1answer
57 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
4answers
159 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 ...
31
votes
11answers
2k views
How could DBAs be more 'programmer friendly'?
The answers and comments on the dba.se version and programmers.se version of the question "What are the arguments against or for putting application logic in the database layer?" are very revealing ...
6
votes
2answers
262 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 ...
16
votes
4answers
1k 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 ...
2
votes
1answer
71 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 ...
63
votes
18answers
8k 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 ...
0
votes
2answers
76 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 ...
3
votes
2answers
236 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
3answers
361 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 ...
3
votes
3answers
311 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
1k 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
105 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
95 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 ...
1
vote
2answers
258 views
Determining sizes for Separated Partitions of programs, data and logs in SQL Server
As per the recommendations of the CIS benchmarks for SQL Server, I want to create separate partitions for the data, the programs and the logs on my SQL Server dedicated server.
Question is, what is ...
5
votes
3answers
162 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
173 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 ...
2
votes
1answer
215 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
375 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? ...
4
votes
1answer
86 views
What are the pitfalls when storing data retrieved from an api
I can get some financial data from our vendor via their API. In this case API is too strong a word. Basically we can get only an array of orders for a given time range. What we paid them for the ...
3
votes
1answer
36 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 ...
1
vote
3answers
90 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 ...
1
vote
1answer
273 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 ...
3
votes
1answer
300 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 ...
6
votes
1answer
538 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
2answers
95 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 ...
2
votes
2answers
97 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 ...
1
vote
1answer
233 views
Normalization: Should common columns of two tables be pulled out into a third table?
Should I further normalize my current design to eliminate redundancies?
I have the following tables:
empl: standard user table
empl_cat: employee "category"
empl_admin_cat: administrative ...
2
votes
1answer
362 views
How to design database for tree view with an infinite depth
I am trying to combine C# and SQL2005 for an application which need to have tree view control.
One of user requests is to that tree view can user friendly grow into infinite dept of child nodes. That ...
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" ...
3
votes
3answers
2k 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 ...
1
vote
2answers
251 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)
...
