Questions tagged [architecture]
The architecture tag has no usage guidance.
181
questions
1
vote
0
answers
413
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 > ...
- 166
1
vote
1
answer
83
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
0
answers
542
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 ...
- 121
1
vote
1
answer
83
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 ...
- 111
0
votes
0
answers
33
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 ...
- 1
3
votes
1
answer
2k
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 ...
- 1,683
1
vote
0
answers
1k
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 ...
- 11
2
votes
2
answers
633
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 ...
user162943
1
vote
0
answers
201
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 ...
- 111
1
vote
0
answers
201
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. ...
- 11
2
votes
2
answers
258
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 ...
- 21
1
vote
1
answer
112
views
Redundant data or UNION [closed]
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)
...
- 4,507
11
votes
1
answer
772
views
Optimizing a 'latest' query in Postgres on 20M rows
My table looks as follows:
Column | Type |
-----------------------+-------------------+
id | integer |
source_id | integer ...
- 111
2
votes
0
answers
998
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 ...
- 525
4
votes
1
answer
147
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 ...
- 155
0
votes
2
answers
98
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 ...
- 101
0
votes
1
answer
161
views
Efficient many to two relationship
I'm building out a database with a users table, and I want to store the mutual (Facebook) friends between every set of two users in the database. Doing this in a normalized, efficient way seems tricky....
- 183
-1
votes
1
answer
329
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 (...
- 351
0
votes
1
answer
588
views
Data Warehouse: Staging Area, DWH databases and ETL tool on one server? [closed]
I am thinking about a DWH server layout and I'd like to ask you if you can spot any downsides to this layout. The basic idea is to have Staging Area & Data Warehouse DBs and the ETL Tool for the ...
- 123
4
votes
1
answer
78
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 ...
- 201
6
votes
1
answer
805
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 ...
- 335
1
vote
1
answer
4k
views
What should be architecture of multi client web application?
My question is not related to a specific technology rather it's about design methodology that should be chosen. My company is about to create a web application whose users will be corporate companies. ...
- 113
0
votes
2
answers
5k
views
Best way to store deleted rows
I have a table with operations that can be deleted by users.
What happens, is that I want to store deleted rows for some time, to serve as recover in case any client regrets the deletion.
One of the ...
- 603
1
vote
0
answers
75
views
Logical data independence
I have a basic doubt about logical data independence. I read that in logical independence, we can remove attributes from relation without affecting external schema.
How can the removal of attribute ...
- 121
1
vote
1
answer
33
views
MS-Access: How do I go about building a database with inconsistent data sources?
I'm working on building a database to aggregate various data sources and feed a set of Tableau dashboards.
The data is all employee data, the problem is, I only have one file of seven with a unique ...
- 111
0
votes
1
answer
368
views
Recursive Seller Account with Balance
I need to build a database for managing Short Messaging Service (SMS).
In this I have 2 types of users
Reseller
Must have starting SMS balance for example: 1,000 - credit table
He can sell ...
- 101
1
vote
2
answers
146
views
Database Roles Security: In SQL Server architecture where does permissions are evaluated in RE or SE or Access methods
I got a question if i create a login and database user for a database named TEST, and i have given permissions to user as db_datareader only.
1) Where does this permission evaluated when a user ...
- 327
-1
votes
1
answer
284
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
1
vote
3
answers
5k
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 ...
- 11
0
votes
1
answer
221
views
Model data with many to many and overrides
I'm having some trouble modeling the database for this particular scenario in Postgres:
We have a set of toys that contain several pieces. Each piece is associated with one or more materials. All of ...
- 101
2
votes
2
answers
111
views
Optimize architecture for SQL Server projects [closed]
I often find myself hacking together multiple .sql files for a project and calling them one after the other through Task Scheduler. Everything I know about SQL is self-taught, so I don't know anything ...
- 21
2
votes
2
answers
8k
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 ...
- 157
0
votes
1
answer
97
views
Database architecture for interactions between users (8B rows, 1TB of data)
I'm saving an interactions table between two users. I have two interaction types, and each interaction can happen more than once. The easiest reference would be Facebook Likes and comments on posts: ...
- 979
2
votes
1
answer
2k
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 ...
13
votes
4
answers
11k
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, ...
- 831
1
vote
1
answer
210
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 ...
- 313
0
votes
1
answer
290
views
SQL Server grant to multiple domains
I am working in a joint venture of four different contractors doing one big project. Many of the application will have to use the different ADFS servers each of the four companies have. Some are ...
- 3
3
votes
2
answers
9k
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.
...
1
vote
1
answer
519
views
Automatic Failover or Database Mirroring?
We have a software backed with database. We want to keep database at a remote location and have it all the time available. For this task, a 5 year old project experience came into my mind. I remember, ...
- 111
-1
votes
1
answer
73
views
Maintaining quantities of products in database [closed]
I am building one online bakery store. Here I will have to manage products like cakes and pastries. A half kg cake could have price of 250 where as 1 kg cake will have price of 450. That is, it doesn'...
- 149
4
votes
1
answer
349
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 ...
- 141
2
votes
1
answer
11k
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 ...
- 1,771
8
votes
3
answers
2k
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 ...
- 81
5
votes
1
answer
14k
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 ...
- 522
3
votes
1
answer
69
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 ...
- 479
1
vote
3
answers
1k
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)
...
- 223
0
votes
1
answer
45
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 ...
- 103
1
vote
1
answer
200
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 ...
- 11
1
vote
0
answers
238
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 ...
- 155
0
votes
2
answers
59
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, ...
