Questions tagged [architecture]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1
vote
1answer
5k views

Changing varchar(max) to varchar(255) requires rebuilding table

Our developers (prior to me being DBA) set the data type for a field to VARCHAR(MAX) when in reality it only needs to be VARCHAR(255). This wasn't discovered until after our table had grown to over ...
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 ...
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 ...
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 ...
1
vote
3answers
136 views

Is it a good practice to isolate login information (username, password) in a separate table?

I have a database table called "patient" where I store the patient information. The structure (list of columns) is as follows. patient table idPatient (int) user_name (varchar) password (varchar) ...
0
votes
1answer
41 views

DB architecture: What if a table is part of another table

My SQL Server database has the initial table named Lead. This table has all kinds of info about a lead person. It has 3 foreign key columns: IndustryId, BusinessTypeId and ReferenceId. I have a ...
1
vote
1answer
126 views

Seeking Suggestion for faster DB Insertion Policy

I have written a server which keeps on receiving concurrent requests from 30K-40K different clients concurrently. Now I need to dump all those individual requests into a database. The database right ...
1
vote
0answers
83 views

SQL Server and Network considerations to transfer backup file to a shared folder

I'm facing with a situation where backups are being transferred manually to a Shared Folder and my goal is to optimize all of this. What I have to deal with is: 8 Windows 2008 R2 Servers 2 Windows ...
0
votes
2answers
46 views

Guidance on inter-server database or data transfer and system architecture

I'm a database developer (not a DBA) responsible for about 20 different non-transactional 1TB SQL Server 2008 - 2012 databases each on its own server. In order to be able to query across databases, ...
1
vote
1answer
199 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` ...
0
votes
1answer
285 views

what shortcomings does couchbase have, compared to mongodb? [closed]

I see a massive gap of popularity between MongoDB and Couchbase. What features\advantages does mongo have over couchbase? I'm deploying DB instances in a cluster, between datacenters, where all ...
1
vote
0answers
172 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 ...
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 ...
1
vote
1answer
671 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__| | ...
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 ...
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 ...
0
votes
1answer
446 views

Django Auth user as well as custom users table?

So we've been using djangos database authentication for a while now and have recently decided we want to implement a completely new login system for our website. We are keeping the django ORM as we ...
0
votes
1answer
106 views

Storing revisions and relations between revised items in 1:N and N:N scenarios for audits

I have several of the following situations in a MySQL schema that I design for a custom CRM/ERP solution. 1:N related tables SO and SO_ITEMS (SO is just one example I have several) that both need to ...
1
vote
1answer
342 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(...
0
votes
0answers
171 views

Is it possible to have Postgresql client libpq.so installed and running on both 32bit and 64bit?

Some time ago I had a problem running with a conflict between the 32bit and 64 bit clients on PostgreSQL which I described here Is my system configuration restricting me to psql:i386?. I have come to ...
1
vote
0answers
44 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-...
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 ...
1
vote
1answer
763 views

PGPool for Postgres load balancing, where to sit it

So I've been doing a stack of reading. I'm still not 100% clear of the best way to go about it. Let's take a simple setup. 3 nodes to server UWSGI apps (in random), 3 nodes for PGSQL, 1 nominal ...
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 ...
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 ...
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 ...
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 ...
1
vote
0answers
178 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 ...
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 ...
6
votes
1answer
870 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 ...
0
votes
2answers
123 views

Options for structuring multiple associations to a record

This is 101 level stuff and I promise to read a book later, but in the meantime, given the following three tables: Product --------- ProductId (uniqueidentifier) User --------- UserId (...
-2
votes
1answer
1k views

Design database for tournaments

I'm looking around since few days on how should I build my database to support full flexible tournaments. Let me explain my specs. As a user I want to : Create a tournament Let other users register ...
2
votes
1answer
275 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 ...
1
vote
1answer
123 views

MySQL - Migrating from Master-Slave replication to Master-Master

We currently run 2 large mysql master-slave replication trees. Each tree is built with one master, several 'middle' slaves replicating from the master, and 'end' slaves replicating from the 'middle' ...
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 (...
1
vote
2answers
1k views

custom product variant upload

I'm building a custom eCommerce solution for a client. I came up with this database design: +---------------+ +---------------+ | PRODUCTS |-----< SKUS | +---------------+ +--...
0
votes
0answers
61 views

custom product variant design [duplicate]

I'm building a custom eCommerce solution for a client. I came up with this database design: +---------------+ +---------------+ | PRODUCTS |-----< SKUS | +---------------+ +--...
2
votes
0answers
85 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 ...
1
vote
4answers
4k views

Oracle redo log buffer cache vs redo log file

While going through the Oracle architecture I got stuck with the below question. Could someone help me answering this? Why do we need both redo log buffer cache and redo log files? Why can't we write ...
2
votes
0answers
625 views

Multiple databases with medium tables or Single database with huge tables

I've developed a shop management software using Postgresql and JAVA. I would like to know which of the solutions below is the best one for my specific situation: Only one database with huge tables (...
0
votes
1answer
2k views

Data distribution in mongos with shards or replica sets

I am a newbie to mongodb.I have set an mongodb architecture with a single mongo shard(mongos) connected to three shards(replica sets) with three config servers. The architecture looks as below: Now ...
1
vote
2answers
748 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 ...
0
votes
3answers
74 views

Redesign basic Mysql storage architecture

We are a small Internet provider and we are storing all our clients traffic data in a MySQL database (MyISAM). We insert data every few minutes in three tables like this: capture_table ---------- ...
0
votes
1answer
790 views

What is a construct in Oracle?

I'm currently studying Oracle Architecture and Database Memory Structures. I'm a newbie in the subject and I just read that a "Shared Pool" is "a data structure that caches various constructs that can ...
1
vote
0answers
98 views

Architecture for a MySQL MDBS

My coworker and I are tasked with redesigning our current Access databases in a MySQL environment. Between the two of us, this wont be a problem but we need to connect to other external databases that ...
0
votes
0answers
40 views

SQL string field lengths in a db having about 6 different types of addresses, with about 20 markets and environments [duplicate]

(Someone suggested I should ask this here too; previously posted on https://softwareengineering.stackexchange.com/questions/231335/sql-string-field-lengths-in-a-db-having-about-6-different-types-of-...
0
votes
1answer
4k views

Digital content eCommerce schema advice? [closed]

I don't have much experience designing databases, I'm looking for some advice on my design from more seasoned designers/developers. I'm developing a digital content eCommerce store which will be ...
1
vote
1answer
1k views

Postgres database zero down time production deployment

I need some idea how I can deploy a financial application in a production environment using PostgreSQL with zero down time. I have two servers in Master-Slave design. Currently I am giving downtime ...
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 ...
2
votes
2answers
191 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. ...