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.
3
votes
1answer
45 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
2answers
46 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
51 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
28 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
68 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, ...
3
votes
2answers
56 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
48 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
39 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
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 ...
2
votes
0answers
39 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
164 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
55 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
72 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
74 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 ...
6
votes
4answers
164 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
60 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
267 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
73 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
77 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
270 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
367 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 ...
1
vote
1answer
163 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 ...
3
votes
3answers
320 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
96 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
222 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
384 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
40 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
166 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 ...
1
vote
1answer
279 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
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 ...
3
votes
1answer
308 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
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
100 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
253 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
156 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
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 ...
2
votes
3answers
227 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
73 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 ...
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 ...
3
votes
2answers
1k 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
91 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
214 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
263 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
193 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
549 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
68 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 ...
