The tag has no usage guidance.

learn more… | top users | synonyms

32
votes
7answers
24k views

Is it better to store images in a BLOB or just the URL? [duplicate]

Possible Duplicate: Files - in the database or not? I was wondering if there's any good reason to still use blob fields in a database. A couple of years ago I worked with a DB with a bunch of ...
19
votes
3answers
300 views

What's the best way to archive all but current year and partition the table at the same time

Task Archive off all but a rolling 13 month period from a group of large tables. The archived data must be stored in another database. The database is in simple recovery mode The tables are 50 mil ...
12
votes
1answer
335 views

Highly Concurrent Storage System

Imagine your requirement is that you have 3 huge tables (structured data) with say 30 billion rows in each (total size of 4TB) and your many concurrent users (which are parallel os threads on remote ...
9
votes
5answers
13k views

Pros/Cons Using multiple databases vs using single database

Working on a new project which has the requirement to use 7 databases. Arguing performance, stability, optimization is easier implemented. While I don't agree I'm having trouble collecting good ...
9
votes
3answers
6k views

Database Administrator vs Database Architect?

In terms of job responsibilities and activities, what is the difference between a Database Administrator (DBA) and a Database Architect?
8
votes
2answers
5k views

What exactly is a database engine?

I have gone through the definition on http://en.wikipedia.org/wiki/Database_engine several times: A database engine (or "storage engine") is the underlying software component that a database ...
8
votes
1answer
3k views

How to store time series data

I have what I believe is a time series data set (please correct me if I'm wrong) that has a bunch of associated values. An example would be modeling a car and tracking its various attributes during a ...
8
votes
1answer
204 views

Relational database: in-RAM partitioning? Theoretical structure discussion

I'm rewriting an MMORPG server engine using some rather esoteric elements (theoretically good but rarely used in practice), and having a bit of doubt. Some elements of this are “solid” — but the point ...
7
votes
2answers
1k views

Is a geographically distributed application with SQL Server replication a good idea?

We are currently thinking about a target architecture for a geographically distributed application. This application tracks and schedules the movements of all material units across various production ...
6
votes
2answers
318 views

Postgres with pgpool architecture

Below is an example pgpool architecture: This implies that you only need to have pgpool on a single server; is this true? When I look at the configuration I also see that you configure backends ...
6
votes
3answers
3k views

Is SQL Server's FILETABLE suitable for storing large files (bigger than 10 GB)?

There is a project for lab. The requirement to save the lab data (raw data and related infomation ) for research for years. So the data must be saved for years, but the data is so big, every raw ...
6
votes
1answer
8k views

MySQL high availability, failover and replication with Latency

We are in the process of implementing a new CMS (Drupal 6.x) that runs on MySQL. We have two data centers -- primary and secondary -- with known latency between them. We're unsure which version of ...
5
votes
1answer
214 views

Multi-Datacenter design for SQL Server

I'm not a DBA and perhaps I can't explain myself properly. I'm looking for some fresh ideas about how, and what I need to deploy, to get a full SQL Server database available across different ...
5
votes
1answer
184 views

Many:Many with Shared Relation

I'm modelling data with multiplicity like this: Each Composition/Anthology related pair must share a Composer. Also, each Anthology must contain at least one Composition. How would you recommend I ...
5
votes
1answer
1k views

In MongoDB, is having lots of small collections better than having one huge collection?

Huge collections have huge indexes that may not fit into RAM. Is that problem entirely solved with sharding ? If I have lots of small collections with small indexes, what are the options for storing ...
5
votes
2answers
291 views

Recommended structure? %70 write %30 read. 10M row. 200 query/sec. Select, update, insert, search

Followings are what I need to do on my project: 1- If a search term is new and unique, then add the search term 2- Show lastest searched 1000 search terms (I don't need to store dates of all search ...
4
votes
2answers
1k views

Architectural Help Needed - MySQL or NoSQL for a table with 54 million records

We have a table with 54 million records. Here is the table structure. CREATE TABLE `metaplay` ( `track_id` int(11) NOT NULL DEFAULT '0', `user_id` int(11) DEFAULT NULL, `completed` int(11) ...
4
votes
2answers
115 views

What could justify a non explicit - non atomic primary key

I'm working as a "guest" dev at a random company. I'm working with a table that does not have a primary key by definition(Oracle). The column named "SOMETHING_ID" is varchar2(10) and constructed at ...
4
votes
1answer
204 views

Do multiple SQL Server instances increase security?

Context Let's assume you have a server which exposes a web server and one or more web services to store and manage sensible information about physical persons (assume, for this example, complete ...
3
votes
2answers
68 views

Efficient schema to handle time interval queries on a huge table?

We have huge table (+10 millions rows) where we aggregate the values by a using a time interval search on a Datetime column. And right now, to build single page on our app, we are querying this table ...
3
votes
4answers
6k views

How to handle table design with variable columns

I have a table design scenario and as a non-DBA type, would like opinions on which is more scaleable. Say you are asked to record information on houses for a metro area, starting with a small ...
3
votes
1answer
74 views

Basic question: Simplest way to share company databases

My company is looking to access large quantities of data from another company we have good relations with. Currently we are using a web interface to interrogate their information, however it is quite ...
3
votes
4answers
371 views

What architecture is best to store a large 3 dimensional array in MySQL?

I am looking to create a large 3 dimensional database in MySQL. The structure will basically be a standard MySQL table, with a time component / dimension added. See the following analogy: { x, y, ...
3
votes
1answer
96 views

Store different rows type in single table

I'd like to create a DB to store different rows type. It will be kind of event list with time stamp. So I'd like to have an Event table with relations to other tables - each table will be a row type (...
3
votes
1answer
276 views

Economic Database Architecture Possibilities w/ Data of Varying Length

I'm in the process of designing a MySQL database (InnoDB) that holds a large amount of economic data which our application scores. The size of the economic data varies a great deal: some measures, ...
3
votes
0answers
32 views

Should I do the maths within PostgreSQL for ACIDity?

It is my first question on Database Administration Stack Exchange. Please be kind if something is not done the nice way.. I would like to be advised concerning the way I'm working on my database, in ...
2
votes
3answers
539 views

Which is better: one database per application, or just one database? [duplicate]

I ask this specifically for SQL Server, the advantages that I can see for multiple databases are: Logs are smaller (so one runaway transaction cannot cause others (replication etc) to stall) A page ...
2
votes
2answers
398 views

Database structure for a system with multisite

The system I'm working is structured as below. Given that I'm planning to use Joomla as the base. a(www.a.com),b(www.b.com),c(www.c.com) are search portals which allows user to to search for ...
2
votes
1answer
184 views

Which databases can meet these replication requirements?

I am doing research on database architectures for a large project, and I am looking for which software can meet my requirements. To try to explain the requirements simply: You have a master ...
2
votes
3answers
137 views

Distributing MySQL database on n systems

I have a webserver in my application which talks to a single MySQL node. Instead of having just one MySQL node, I want to have a cluster of n nodes which form my database. Is it possible to do ...
2
votes
2answers
484 views

Storing revisions in SQL Server (simply much like TFS)

So, what I've got so far: Table: ChangeSets Columns: Id | Comment | Committer Table: Customers Columns: Id | Name Table: CustomerChangeSets Columns: Id | CustomerId (FK) | ChangeSetId (FK) So ...
2
votes
1answer
601 views

user permission cache in session or check from database?

On each page of my websites 2 or 3 permissions have to be checked and I use role-permission model. Which one is better? to check permissions of each user always through database directly or once ...
2
votes
1answer
1k views

Foreign key with multiple table options

I'm not sure if the title is very good, feel free to suggest a better one. People can "comment" on different objects of my site,similarly to StackExchange (for example they can comment on a Question, ...
2
votes
1answer
379 views

Redesigning a multi-instance MySQL server for performance and resiliency

I've got a single MySQL server which currently runs N separate MySQL instances on, which are rapidly growing beyond the capabilities of that box. Is it possible to have an active-active MySQL cluster,...
2
votes
1answer
102 views

What is the best architecture to run Oracle sucessfully run on VMWare?

Due to a data center move we are mandated to migrate all servers to virtual machines. We currently have a 3 Node Oracle RAC running on physical servers. There seems to be a great debate about the ...
2
votes
0answers
53 views

Cassandra Deployement Architecture

I'm new to Cassandra. I have the responsibility to design a basic deployment architecture for a use case. I'd like to know if my solution is correct, and get advice and recommendations from you. Here ...
2
votes
0answers
208 views

SQL Server, Mount Points and File Groups design [closed]

I am looking for some advice on a data warehouse we are building. Specifically around SQL Server, mount points and file groups. We have two design options for how mount points and files groups should ...
2
votes
2answers
142 views

Oracle database Architecture

I need to know to correct order between these following steps, it's hard to find information on the internet on what happens first, i'd be really grateful if someone could give me the answer A. ...
1
vote
2answers
254 views

How to architect an analytical BI platform? [closed]

I am working on a new BI project for a medium sized corporation. Right now there is no Analytical infrastructure in place and the reporting is done manually in Excel. There are a few different data ...
1
vote
1answer
128 views

Where do caching servers conceptually fit in my architecture?

Given the simple well-known architecture below: Where (conceptually) does the caching server(s) sit?
1
vote
1answer
64 views

Do regular ALTERs weaken the performance?

In a project, we need to frequently ALTER the tables to delete or add columns. I wonder if this weakens the database performance in long term. If it has a considerable negative impact, what is the ...
1
vote
1answer
540 views

How do I handle importing high amounts of transaction data into application DB?

For the moment, this is a hypothetical question. I've got a database that has a high level of transactional data coming into it. I also have a database that supports my website (my application ...
1
vote
1answer
41 views

Design db schema in correct way for parent child kind of requirement

I am creating schema of tables. But not able to understand best way to do it. Following are the tables: Car Model - It contains all car models with types. For eg. Mercedes(brand) - GLA 250(...
1
vote
1answer
141 views

Storing all data in a single db versus multiple dbs in Mongodb

We have an app that is basically an ERP. Some of the resources in the ERP are satellite tracked. So the information related to the resource itself is minimal, but the satellite tracker produces data ...
1
vote
1answer
1k views

MySQL Cluster over WAN: Best Architecture Options?

My company is looking at a multi-data-centre solution for high-availability, fun, and profit. We have businesses across the planet, with the need for MySQL database clustering solutions that can keep ...
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` ...
1
vote
0answers
15 views

Mysql multisource replication : on slave map masters dbname to something like mastername-originaldbname

I need to implement a mysql multisource replication using mysql >=5.7.11, there are already a certain number of mysql servers running which will become masters for the single slave which is going to ...
1
vote
0answers
28 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__| | ...
1
vote
0answers
40 views

Real-time Synchronization [duplicate]

I have created an application in .Net with Microsoft SQL Server for desktop with a local database and an online application with an online database. Data entry will be done on both platforms in real-...
1
vote
0answers
149 views

Generally unsolvable problem? How to ORDER BY aggregated columns GROUPED BY many possible columns very fast?

Is there general solution or some best practises or at least some vague hint, how to solve following problem (general, but concretised in following example)? I think this must have been solved many ...