Questions tagged [architecture]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
38
votes
7answers
36k 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 ...
23
votes
3answers
5k 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 ...
22
votes
2answers
11k 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 ...
16
votes
4answers
28k 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 ...
14
votes
5answers
25k views

Pros/Cons Using multiple databases vs using single database

I was 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 ...
12
votes
4answers
5k views

Drives vs. Mount Points?

The previous Senior DBA set up mount points for all of our drives across every SQL Server throughout the company. The new Senior DBA is horrified by mount points wants to change our standard (mainly, ...
12
votes
2answers
10k 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 ...
12
votes
3answers
10k 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?
12
votes
1answer
381 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 ...
10
votes
1answer
296 views

Optimizing a 'latest' query in Postgres on 20M rows

My table looks as follows: Column | Type | -----------------------+-------------------+ id | integer | source_id | integer ...
9
votes
2answers
2k 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 ...
8
votes
1answer
10k 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 ...
7
votes
2answers
2k 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 ...
7
votes
1answer
227 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
3answers
1k views

Data warehouse staging architecture

This is a question about data warehouse design. We are setting up a healthcare datawarehouse and starting with 2 major source systems that combine for about 20,000 tables and 2 TB of data. 1) It is ...
6
votes
3answers
6k 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
616 views

Entity-relationship diagrams and application program functionalities

It seems that the core functionalities of an application program I'm working on are nothing but associative entities. Hence one-to-many relations sort of produce "metadata" that will only feed (one ...
6
votes
1answer
2k 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 ...
6
votes
1answer
2k 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 ...
6
votes
1answer
877 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 ...
5
votes
2answers
221 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 ...
5
votes
1answer
204 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
2answers
317 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
1answer
133 views

Split Brain Administrators

I have been appointed the DBA for our organization. I have been asked by senior officials to allow the two divisions of IT to have full control of their databases. They have asked for the following ...
4
votes
1answer
5k views

Stored Procedure Processing and Error Log

I used to work for a company where they had this 3rd party Data warehouse Solution. Obviously all the objects and tables were hidden in the Supporting database so I don't have clear idea on what ...
4
votes
1answer
174 views

Numerical data with lots of NULLs: 6NF or document database?

I have a source of numerical data mostly comprised of sensor readings, physical quantities and flags. Each associated with a datetime. | id | timetag | sensor1 | sensor2 ...
4
votes
1answer
2k 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 ...
4
votes
1answer
62 views

Approach to Handling Errors/DataCorrections in Aggregated Data

We're in the process of building a vNext Data/Reporting Platform for capturing Web & Sales Traffic. At a very high level we're capturing Impressions, Clicks & Sales Data which then needs to ...
3
votes
3answers
2k 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 ...
3
votes
1answer
344 views

Should SSIS packages and SQL database be on same server?

I'm a bit new to SSIS. I have a lot of 'dev', building, query, tuning performance on SQL Server but am not the master of admin/ overall performance modeling. So here's my question --- Should SSIS ...
3
votes
1answer
77 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 ...
3
votes
2answers
1k 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
1answer
98 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
1k 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, z ...
3
votes
1answer
58 views

should I perpetuate bigint?

I have a base table used everywhere called Customers. Of course, its unlikely that table will ever grow past int size, but it is keyed on a bigint nonetheless. I don't believe that I can change the ...
3
votes
1answer
105 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
381 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, ...
2
votes
1answer
910 views

What is a database mission-statement?

What exactly is a database mission-statement? My question was prompted after submitting an academic-assignment requiring a "mission-statement" for ERD-modeling. I've yet to encounter this ...
2
votes
2answers
627 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
2answers
3k views

Sync two databases on different servers

I can't pick the right solution for my below requirement. Can you people please advise? We have two SQL Server 2012 database servers: First one is in Texas (USA). Our client will use this for UAT. ...
2
votes
1answer
276 views

Which databases can meet these replication requirements?

I am doing research on database architectures, and I am looking for which software can meet my requirements. To try to explain the requirements simply: You have a master database server. This may ...
2
votes
2answers
750 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
3answers
150 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
156 views

Difference between OLTP and Inmon Data Warehouse

What is the general difference between OLTP and Inmon-style Datawarehouse? It seems like they are the same. OLTP just copies all the tables into a Read-Only Datawarehouse DB. I know what a Kimball ...
2
votes
1answer
5k views

How shared nothing and shared everything architectures look like in Oracle?

Oracle has partitioning and clustering features. Partitioning enables to split table or indexes into multiple tablespaces and store on various servers. So it's done manually. Clustering enables ...
2
votes
2answers
85 views

Does Log File contain both committed and uncommitted transactions?

My question is related to SQL Server architecture. Mainly log file. In the picture below it says that when transaction is committed, log record in LOG CACHE will be moved to Log file. Does it mean ...
2
votes
2answers
4k views

SQL Server 2016 Multiple Availability Groups and instances

So we are getting ready to make a major architecture shift and I get to be on the hook for it, yay! Currently we have 22 separate availability groups each with their own windows cluster across 44 ...
2
votes
1answer
242 views

Horizontal and Vertical Fragmentation on Distributed Database

My Architecture: I need to build a database with SQL Server, where the data from each server is distributed to the other server. How can I achieve this kind of architecture? How do I maintain ...
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, ...