Questions tagged [architecture]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
0answers
27 views

DB scheme for the home exchange site: store date ranges or individual days for availability

I am creating a db schema for a home exchange site. Host can set his home as exposed for exchange on certain dates. Guest must be able to view available dates. If exposed date is free (not reserved ...
0
votes
0answers
15 views

Database for subscription system with multiple opt-in criteria

I want to create a subscription system for app users. I guess I need at least two different tables: 1) One table will contain the user preference for how often does he wish to get an update, for ...
14
votes
5answers
27k views

Pros/Cons of using multiple databases vs using a single database

I was working on a new project which has the requirement to use 7 databases, arguing that performance, stability, optimization are more easily implemented. While I don't agree, I'm having trouble ...
1
vote
1answer
48 views

The best practice for SQL + NoSQL database design [closed]

I’m in process of designing database architecture for my diploma project and I have second thoughts about different solutions. I know and understand all my objectives can be done within relational ...
-1
votes
2answers
40 views

Separate table or a enum on a client? [closed]

I have several tables and many of them has a "status", "measure_unit" and the like columns in them. There're 2 approaches to work with columns such as "status". Create separate tables "statuses", "...
2
votes
1answer
137 views

Permission for business logic layer & application

I'm very new into db development and currently working on my first production app. I learned that I would need a business logic layer (BLL) to authenticate and authorize users, for example: John can ...
1
vote
1answer
26 views

Literature about In-Memory Database Systems [closed]

I am currently writing my bachelor thesis on IMDBS and would like to ask if any of you have any recommendations for literature which summarizes the technical conception/architecture of IMDBS as ...
0
votes
1answer
149 views

RAID drives or replicate data?

I have an old Microsoft SQL Server that, for financial reasons, I cannot replace until later in the year. The OS, DB and temp files are on one 10k RAID 5 array (I didn't design it) The server is ...
0
votes
1answer
31 views

Reports with recurring events DB Schema in SQL Server

I'm trying to create a scheduling reporting feature where a user can schedule a report to be received "every 1st Monday of the month" or "every last day of the month" or "every week on Tuesday". I ...
0
votes
1answer
49 views

String or separate Table

I'm creating the DB for a system and in it is an alert system, where, when a certain action happens, or when certain information is present, an alert (via email) is triggered. I'm allowing users to ...
1
vote
1answer
36 views

Need an Audit Log. Should I create an Audit Log Table or Audit Fields in each table?

By no means am I a DB expert, but I do have experience with creating DBs. Not sure about the following though. I want everything that happens in the system to be trackable. My client is dealing with ...
0
votes
2answers
142 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 (...
0
votes
1answer
181 views

Looking for an efficient way to build some kind of “FIFO” table

I need to store measured data on a daily basis for a given period in the future, lets say 7 days. The current day should be always day 1. Basic Example: | id | day | value | |----|--------|-------|...
0
votes
1answer
25 views

does automatic checkpoint flushes dirty pages which are related to un-committed transactions to MDF File in SQL Server

when an automatic checpoint runs in background in sql server, i know it flushes all the dirty pages to data file. does it also flushes dirty pages which are related to un-committed transactions into ...
0
votes
0answers
39 views

What's the best way to model similar tables with overlapping functionality?

I'm building a sports event/tournament/league management app for myself, and I'm stuck on the best way to model how Teams should relate to the different competition types. Some background regarding ...
0
votes
1answer
114 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
votes
1answer
228 views

Selective synchronization between distributed clusters

What's the best practice for the following scenario: I've an embeded device with a single, lightweight database; a powerful server with a cluster; a cluster in the cloud. I'd like to synchronize the ...
-1
votes
1answer
227 views

Archiving strategy

I have an instance with only one database which contains 4 years of data (400 gigas). I name this instance inst_live I want to implement this following archiving strategy: create an archive instance (...
0
votes
1answer
25 views

Database Architecture Identifiers

I am kind of new around DBA world, i am currently working with JMeter(front/back end performance testing) but when i have spare time (which is a lot lately..) i try to learn about DBA Development, ...
2
votes
2answers
213 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 ...
0
votes
3answers
70 views

Database design for monthly leaderboards

I have a system where users can make predictions on events. There are about two events a week. When the event ends the system scores all the predictions and updates this in the database. Here is an ...
1
vote
0answers
387 views

Interesting Database Designs for Product Catalogue including a Recursive Approach

I am doing some work for a client and they are creating a database of products of which I need to consume product data when it's created and updated to store in a third party service and then present ...
1
vote
1answer
32 views

How to query for address “predictions” while applying constraints

Let's say I have this simplified database scheme. The scheme consists of three tables, street, city and zipcode that all have a many-to-many relationship to each other. What I want to do is a ...
-3
votes
1answer
32 views

Which database to use? Storing one string every second for several days [closed]

I would like to observe some cryptocurrency prices and for that, I want to store a string, everey second in a database for ca. 3 days. The string will vary in length, it will be 2500 characters max. (...
1
vote
3answers
3k views

Primary and secondary replicas on same server

We're considering different options for HA/DR with SQL Server 2016. AlwaysOn Availability Groups are obviously an option. I understand we can read from secondary replicas, unlike DB mirroring for ...
1
vote
1answer
42 views

Reducing the number of tables used - mySQL

I have a table for tax implementation in my application, but I am not sure the design I have done for the table. Can someone review it? val_taxes is the table which has all types of tax listed in ...
0
votes
0answers
64 views

Data integrity in one-to-many distributed architecture

We are a retail shop that has a central data repository which collects data continuously from geographically distributed 1500 odd servers (each store has one SQL Server instance). Like a one to many ...
10
votes
1answer
326 views

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

My table looks as follows: Column | Type | -----------------------+-------------------+ id | integer | source_id | integer ...
6
votes
1answer
630 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 ...
2
votes
1answer
357 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 ...
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 ...
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 ...
1
vote
0answers
190 views

Performance benefits using gRPC to communicate with Postgresql from Web? [closed]

Standard REST server is rather simple to understand and get up and running. But a gRPC server that do the same thing seems to be more complicated. Are there many that uses the concept web > gRPC > ...
0
votes
0answers
10 views

SolR data location after HDFS indexing

I have a requirement to evaluate a Big Data solution based on SolR but I have some questions unanswered after my search. The solution need to provide access and search capabilities to structural data ...
1
vote
1answer
50 views

Architecture design for a large number of db records upgrade

Honestly struggled a bit with the title of the question. Here is the deal. I have a specific set of db operations on various tables across an db. In a simpler sense it's kind of a migration, that I ...
2
votes
0answers
192 views

PostgreSQL: Having many instances of one application should I go with database per instance or schema per instance? [closed]

I am not asking for an opinion here. I need an answer supported by arguments. We have an web-application that will be installed multiple times on one server (one instance per customer). How should I ...
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 ...
3
votes
1answer
493 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 ...
0
votes
0answers
29 views

Advice Regarding Database Design

I am working on a poory designed application, so please do not judge me by the work i have been posed. I have servers table whose data can be visualised as and attributes table which can be ...
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 ...
2
votes
2answers
178 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 ...
1
vote
0answers
80 views

Coffee shop static tag/attribute querying (satisfying AND & OR condition groups) - Relational DB/DBMS or NoSQL?

Let's say you are storing information about coffee shops, and you want to assign an arbitrary number/assortment of static tags/labels to each cafe which act like attributes (i.e., the cafe either has ...
1
vote
1answer
128 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 ...
0
votes
1answer
2k views

Advantages of “middle” tables in one-to-many relationships

Entities: Lists Leads What I am trying to achieve is a scenerio where in the relationship between lists and leads the following constraint is applied: A list can have many leads, but a lead can only ...
1
vote
0answers
60 views

A flexible and robust schema for storing precomputed values/metrics

Background I'm dealing with a table that contains a lot of historical data. What I am doing is generating a bunch of metrics based on different combinations of column values and date periods (e.g. ...
2
votes
2answers
88 views

Splitting the servers

We have a Windows 2012 VM running SQL Server 2012. There are around 20 databases with a total size of 5.83 TBs. There is 64GB of RAM and 16 logical processors on the box. The server is extremely busy ...
1
vote
1answer
71 views

Redundant data or UNION

I couldn't come up with better title. I'm preparing data structure for GDPR and I ran into one problem. I have two types of legal basis for storing the personal data: Law User specific (consent) ...
1
vote
0answers
356 views

Designing a PostgreSQL database for high-volume updates

I am designing a database for a system that has to handle lots of writes operations (99%+ are updates, sometimes inserts) but at all times it has to provide fast reads. At the moment selects take a ...
0
votes
2answers
65 views

Database design for payment feature system

I am building a system where visitors can buy modules from my website. For now I only have 3 available, but of course this number will keep growing. In order to facilitate this in my MySQL database ...
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 ...