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.
2
votes
0answers
158 views
going from heirarchical XML to heirarchical SQL tables then SQL to c# objects to go to user view, how should I store the data in the databases?
Hi I'm getting my data from a web service using their getData call and it comes in as fairly chunky heirarchical XML, first everything all at once, like
<allData>
<product1>
...
6
votes
3answers
155 views
How to handle reporting of an extreme amount of databases in SQL Server 2005?
I'm looking for some advice on how to handle reporting for our environment. We currently have 16 servers with 20 instances of SQL Server 2005. We have over 6,600 databases and growing across these ...
2
votes
1answer
180 views
How would you implement conversation groups/ locking for multiple users of a Service Broker Queue?
How do you implement conversation groups for multiple users using their own instance of the user applications but sending related messages to the service broker queue? Any good example of this kind ...
5
votes
1answer
405 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
220 views
Should I use mysql_upgrade after mysqld_safe when upgrading MariaDB?
I'm wanting to know the best practice to do a MariaDB upgrade on opensuse.
I am getting this error:
mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db =
'a_database'': Cannot load from ...
1
vote
0answers
94 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
2k views
Using Same CASE WHEN Conditions For Multiple Query Columns
Is there a "better" way to rewrite a SELECT clause where multiple columns use the same CASE WHEN conditions so that the conditions are only checked once?
See the example below.
SELECT
CASE ...
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 ...
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 ...
2
votes
2answers
563 views
Looking for example design of using an SQL database as a queue for throttling web service requests of multiple users
I'm designing a system for multiple users to queue their requests to a web service which takes requests such as creating new entries, changing entries, requesting data. Many of the web service calls ...
3
votes
1answer
187 views
Optimizing queries against a set of tables that contain partitioned data
I have a third party program that creates and populates a series of tables to hold some historical data:
data_33 -- contains data for 12/01/11 - 12/05/11
data_34 -- contains data for 12/05/11 - ...
0
votes
1answer
636 views
Best practices for ensuring up to date sequences after table import
We perform weekly table moves between several schema using expdp and impdp. Some tables have sequences associated with the ID columns and I'd like to ensure that the sequences are always up to date on ...
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
3answers
145 views
What are the design alternatives when having multiple “type” tables
I am working on a database design and see myself with lots of *_type tables (e.g. user_type, product_type, etc.) where the structure of these tables is the same, basically:
user_type (
id int pk
...
0
votes
1answer
78 views
Best practises in securing Oracle Standard edition one [closed]
I have a database server with Oracle Standard Edition One. I'm interested what are the best practices to secure Oracle? I have Java web application installed in the same server. The Java application ...
7
votes
1answer
420 views
Why does the fill factor in SQL Server default to 0 (100%)?
I understand fill-factor, pages and index structure and I therefor understand why a 100% fill factor is a rare best practice. So why does it default to 0 (or 100% ) by default? Why not 90 or 95?
Is ...
4
votes
3answers
648 views
“Table of Constants” - is this common practice?
College SQL class, using the book "SQL Fundamentals" by John J. Patrick. In the third chapter, he talks about using a "table of constants" to add columns to a select statement, where all rows have the ...
2
votes
1answer
313 views
Replicate SQL Data Inside Firewall to Outside Firewall - Suggestions
Currently I have an internal CRM application that houses all my data and runs my entire company. The front end is ASP, and the back end is SQL. The external site will run SQL
What I'd like to do ...
12
votes
4answers
2k views
Where should you define foreign keys?
Is it better to define foreign keys in the database or in the code part of an application?
4
votes
2answers
233 views
Huge database logging of event type rows and ways to optimize it
We have a database that stores events. Events are generated at a 1000 per sec rate. We need to keep these events accessible for some years.
Usual use of these events is selecting some of them from the ...
5
votes
2answers
236 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.
...
2
votes
1answer
409 views
tempdb on raid1 isolated or raid10 shared with high write db
PROPOSAL 2
8x600gb raid 10 mdf array
4x600gb raid 10 ldf array
PROPOSAL 3
8x600gb raid 10 mdf array
2x600gb raid 1 ldf array
2x600gb raid 1 tempdb mdf/ldf array
READ/WRITE STATS ON ...
3
votes
1answer
545 views
minimum privileges required for a user
I am configuring a 10g Express database on Windows 2003 that will be the back end for a public web server. There are many other things to do to harden the database but a good place to start is user ...
-3
votes
1answer
293 views
Best practices for finding and updating a handful of records?
Seems like a simple question, but wondering what is the "best" way to do this if you were "dotting your i's and crossing your t's".
7
votes
1answer
475 views
How to setup local database development process for small web team?
Background
I am working on creating a new development process for a small web team of about 4 programmers and 4 designers, with the obvious potential to grow the team in the future.
Our product is a ...
2
votes
2answers
246 views
How to quantify TEMPDB usage in relation to other databases hosted on same hardware?
Working on a project to decide if configuration 1 or 2 is the best way to organise disks for my SAN:
Config1 (sql 2008):
8 disks @ raid 10 data
2 disks @ raid 1 logs
2 disks @ raid 1 tempdb
2 disks ...
4
votes
1answer
342 views
What is the optimal way to design an approval queue and audit trail for entities in SQL Server 2008 R2?
Simplified Explanation: I have three main tables: customer, phone, and customer_phone to resolve the many-to-many. I need to ensure that only admins can insert/update these tables without approval. ...
9
votes
5answers
4k views
Splitting Tables in MySQL. Good practice?
I have started working on an existing project and the previous developer had split up a table into 10 separate tables with identical schemas but different data.
The tables look like this:
...
1
vote
2answers
3k views
How to join two table and show one query result in MySQL?
I have a table1 (records 3), and table2 (records 3). Where i have field name in both. Now i want to make a result from those two table, which will show me both table records and take only one if there ...
12
votes
1answer
2k views
Best practices for schema changes and data migrations to a live database without downtime?
How do you make schema changes to a live database without downtime?
For instance, lets say I have a PostgreSQL database with a table including various user data like email addresses etc, all ...
2
votes
3answers
388 views
View Design Best Practices
When designing a view, would it be best to retrieve
the ID fields where FK relationships exist between the joined tables
the desired description field obtained through the FK id relationship
both?
...
2
votes
1answer
3k views
Multiple left joins or subquery?
I'm working on joining multiple tables together to extract some data related to user "runs", this includes basics like runID, username etc but also joins to the run times and favourites.
I have the ...
5
votes
2answers
2k views
Storing time with milliseconds in database
Just a quick question regarding storing "time" in a database. I'm logging the time of users runs which are in the format 00:00:00:00 (hours, minutes, seconds, milliseconds).
I was originally going to ...
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 ...
2
votes
2answers
617 views
Best practices for creating partition schemes and functions
I'm implementing partitioning for the first time in SQL Server 2008 R2. I've several large tables that I am partitioning.
My main question is: Should I use the same Partition Scheme and Function for ...
3
votes
1answer
481 views
Nested queries or temp (#) tables in SQL Server
I have written a web application which aggregates and presents data from a SQL Server. The data involved is large, and there is a lot of aggregation involved. Currently, under advice from an 'expert' ...
2
votes
2answers
292 views
Is there any prevalent logging packages in PL/SQL similar to java and .Net counterparts
Normally in java or C# , I would use a following construct when try and catch errors.
Bad Exception Handling in C# and Java
try
{
// do something
}
catch(Exception ex)
{
}
Good Exception ...
3
votes
1answer
128 views
Possible to add KEEPFIXED PLAN option to reduce the recompile in SqlServer2k5 on Production Env?
I'm not sure if it is good to enable the KEEPFIXED PLAN to reduce the impact of recompilation in the Production Env?
I'd know if we may gain performance after that or on the other hand it will be ...
10
votes
4answers
4k views
Should dbo schema be avoided?
When it comes to the dbo schema:
Is it a best practice to avoid using the dbo schema when creating database objects?
Why should the dbo schema be avoided or should it?
Which database user should own ...
3
votes
1answer
299 views
SQL Globalization - DateTimeOffset
What is the recommended best practice when it comes to storing date/time for an application that needs to support multiple timezones?
Which is best ?
Use the SQL DateTime data type and insert ...
0
votes
1answer
121 views
What are the first necessary actions needed to help secure a database's schema & data? [closed]
This smells like an open-ended, opinionated, wiki free-for-all, but I'm not trying to make this a battle of opinions.
It seems like there should be a good checklist out there for all to agree on ...
6
votes
5answers
375 views
Over use/correct use of schemas?
Having asked This question on Stackoverflow, I wondered where what I have done is correct/best practise.
Basically, every object that I create is going into a schema with the schema name reflecting a ...
3
votes
1answer
412 views
Webapp & MySQL: Row Level Security
I am trying to emulate row level security on a webapp I am developing using MySQL.
Using this method:
Creating a database with the the required tables where the data pertaining to all the users will ...
2
votes
1answer
204 views
Decision tables in the database: a valid solution?
I have to extend a .Net 4/C# case management application that uses Oracle 11g to handle legal cases.
I have minimal budget, my experience with Oracle and a very junior C# developer to accomplish ...
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
1answer
235 views
Storing Passport Numbers in a database
We're developing a travel/tourist application for a tours company who want to be able to input traveler information into an online database.
Is there any security best practice for storing passport ...
1
vote
1answer
158 views
Design Tagging module in the database
I've a database system which we want to define tags for many tables into it. For example I've videos, photos, ... different entities that we wish to make them taggable. The tags could be anything. By ...
7
votes
4answers
1k views
Help me choose a RAID level combination for a SQL Server 2008 instance
I am going to rebuild one IBM 3400 server from scratch. This server is dedicated to a SQL Server 2008 instance running on Windows 2008 R2.
I am going to make new RAID configuration. I have 6 SCSI ...
2
votes
0answers
174 views
Stored procedures over SQL in the code, good idea? [duplicate]
Possible Duplicate:
What are the arguments against or for putting application logic in the database layer?
Assuming I got over the problem of maintainability and debugging.
My assumption is ...
2
votes
2answers
305 views
What reason is there to create a physical table for a report output?
I've noticed that for Crystal Reports made by our organization and by some of our ERA software providers have a tendency to use physical tables for their reports' data sets, rather than using a view ...
