This tag is for general database questions; if you question is specific to SQL use that tag instead.

learn more… | top users | synonyms (1)

38
votes
8answers
5k views

Why would you store an enum in DB?

I've seen a number of questions, like this, asking for advice on how to store enums in DB. But I wonder why would you do that. So let's say that I have an entity Person with a gender field, and a ...
5
votes
2answers
188 views

How to store ordered information in a Relational Database

I am trying to understand how to properly store ordered information in a relational database. An example: Say I have a Playlist, consisting of Songs. Inside my Relational Database, I have a table of ...
3
votes
1answer
96 views

Executing queries against multiple separate database instances

Premise I have a number of database servers (different DBMS instances, installations in different physical locations), each of them contains data and uses the same schema. Most of the data is ...
3
votes
3answers
78 views

Handling Value Unit data

We have some data that is strongly bound to each other and we are using it for calculations. Its a value with a unit type and an optional relation. E.g. 1500 meter above sea level. The unit type ...
4
votes
2answers
161 views

What is the recommended strategy to deal with a frequently changing database model during development

A lot of projects have a to be determined database model during early development. This could mean that a lot of SQL will have to be rewritten frequently. What is the recommended strategy to deal with ...
5
votes
3answers
185 views

Is it a good practice to have a pre-filled embedded database for development?

I'm currently working on a project which only uses its database for data storage. This means there are no triggers or stored procedure in it, just tables and data to put into it. In this scenario I'm ...
2
votes
3answers
182 views

Enforce uniqueness of personal contact records in a system or not? [closed]

I have a contact table which stores all the contact information of a person such as name, address, phone number. The system would be more user friendly if it can prevent users from accidentally ...
0
votes
0answers
33 views

Where should user input be validated? [duplicate]

I am working on a webbased user interface using AngularJS. As a database I am using MongoDB which can be accessed through an Python-eve-API. The API is able to validate the passed data as stated in ...
3
votes
2answers
137 views

How do I model a sortable resource in sql database? [closed]

I encounter this issue when I create REST API. I cannot tell the exact scenario, but let's say it is for a website that has category and product resources. Each category has many products and both ...
12
votes
5answers
802 views

How to avoid violating the SRP in a class to manage caching?

Note: The code sample is written in c#, but that shouldn't matter. I've put c# as a tag because I can't find a more appropiate one. This is about the code structure. I'm reading Clean Code and trying ...
32
votes
6answers
4k views

What is a good security practice for storing a critical database on developer's laptops?

We have a few givens: Developers need a replica of the production database on their machines. Developers have the password to said database in the App.config files. We don't want the data in ...
2
votes
0answers
90 views

Upload, convert, and store multiple different product data feeds into a database

I'm not sure if this right location for this question, but here it is. I currently have about 30 different CSV or TXT product data feeds. I am trying to build a tool to 1) upload each feed 2) ...
4
votes
2answers
52 views

AutoIncrement Integer Column,Guid or PGuid, which to choose?

Guid has been a Global Unique Identification. Why does "PGuid" (Global Unique Identification) appear? 1) Is Guid NOT PERMENT for unique? (I sometimes wonder whether Guid is REALLY PERMENT generator ...
3
votes
1answer
113 views

Django Migrations: Binds data to code?

If you have custom code for models (custom validators and custom fields are examples), Django Migrations will import them directly into the migration files. An example: file web/models.py def ...
3
votes
2answers
204 views

One wide table or multiple themed tables?

I'm trying to design a database for a simple text based game where the player characters have a large number of statistics that I want to track. Currently I have a few groups of related statistics, ...
12
votes
1answer
316 views

Can domain models in the database be a sustainable solution?

I just started at a new job as a database developer for a medium-small sized, company based on Microsoft-technology. I noticed early on how much practices deviate from what I've been taught at school ...
2
votes
2answers
223 views

O(1) complexity array access

I have an array of heights, with irregularly spread stations, e.g.: arrH=[100 500 1000 2500 4500]. During run-time I receive some height, h, and I need to determine which cell (index) contains the ...
2
votes
1answer
191 views

Elasticsearch and relational database combination

Imagine I have a marketplace application - where users can search for products (we concentrate on clothes). Every product has an ID, name (text), description (text), price (numeric), size (numeric), ...
2
votes
2answers
313 views

Why do some prominent web sites use alphanumeric strings for resource IDs instead of numbers?

For example, the video IDs that YouTube uses are not a simple number, but instead a case-sensitive, alphanumeric string (like dQw4w9WgXcQ). Why do some websites, such as YouTube and Dailymotion use an ...
0
votes
0answers
53 views

Designing how to add multiple items for a user in a database?

I'm relatively new into designing databases so this is my first project and I'm trying to understand better it's structure. I need to create a SQLite database where there will be a simple USERS table ...
2
votes
3answers
86 views

A pattern to find out if data in a dialog-window was modified by the user?

I think about if there is a pattern or another intelligent solution for that problem. I am searching for a more generalized and reusable solution. I load an existing and still persistent row/object ...
0
votes
4answers
138 views

How to avoid null in primary key? [closed]

I need to make table that seems to require two column primary key with one of the columns nullable. But according to questions I read on Stack Overflow that is undesirable and even against SQL ...
2
votes
2answers
1k views

How are abstract database interfaces written to support multiple database types?

How does one begin to design an abstract class in their larger application that can interface with several types of databases, such as MySQL, SQLLite, MSSQL, etc? What is this design pattern called ...
4
votes
3answers
291 views

How to avoid the LoadBy pattern?

In our codebase for a sophisticated system controlling many aspects of businesses, we have a certain unwritten law to not fudge around in code with complicated SQL, instead writing it into a function ...
1
vote
2answers
76 views

Working with local connections instead of global

In recent weeks I became very concerned that the way I've been working with database connections for the past years, is simply wrong. I exclusively create a new connection for each database operation ...
0
votes
0answers
68 views

Persisting and manipulating JS ArrayBuffer data on the server

My node.js app has to deal with (sometimes large; a few MB) tables of numerical data. I'm about to use JavaScript's native binary objects for this purpose (i.e. ArrayBuffer and friends), because they'...
0
votes
1answer
134 views

Ruby best practices for Data Access layers

I'm starting my way into Ruby development and have some questions that I hope you can give your best opinion to better design some command line applications I'm developing. What are the best patterns ...
1
vote
3answers
189 views

Should I create a class for functions that uses of the database?

I have a PHP application which is pretty simple: It allows the user to create, edit and read a post. Basic stuff. I have a Database class which handles the connection and the queries execution. The ...
1
vote
1answer
49 views

Search by Location and Keywords [closed]

I have data set with ~5M entries/rows (~2GB). Every entry contains a location field (lat/lon coordinate-pair) and n keyword fields (keyword-1, keyword-2, ... keyword-n). The keyword-fields can all ...
31
votes
5answers
4k views

No central database

I have a client that is looking to get a website/mobile apps/desktop apps built that deal with very sensitive data (more sensitive than bank/card details). Because of the sensitive nature of the data, ...
2
votes
1answer
324 views

OOP - Should I set the database connection as `public` for other classes to use it, although it might be unsafe?

I have a basic Database class, and it has a mysqli property (an object itself) which contains the connection information. Other classes uses this class to read and manipulate the database. There are ...
8
votes
4answers
2k views

Will the Database connection be closed if we yield the datareader row and not read all the records?

While understanding how yield keyword works, I came across link1 and link2 on StackOverflow which advocates the use of yield return while iterating over the DataReader and it suits my need as well. ...
0
votes
1answer
39 views

Database multiple database query each time

I'm writing a rest service. For each request I use about 4-8 queries. Should I try to refactor those into a single query?
0
votes
1answer
203 views

Efficient way to store large number of image path per record into a database table? [closed]

I am trying to think of an efficient way to achieve this. We get a zip file that has a XML file containing around 1500-2000 items. Each of the item could have up to 20 images for it. The images are ...
0
votes
3answers
82 views

Identical Databases for Multiple Users Reading Efficiency

Our project utilizes a static database (no writes). With many users we don't actually have a problem per se reading it. If a database "locks" per read request by any user, then would it not be more ...
2
votes
3answers
179 views

Long running DB query with concurrent write at the same time [closed]

We have a big table with a lot of data and a select query which takes 3 seconds to run. However we are a highly concurrent environment and each second we get 100 new records in our database. So let'...
0
votes
2answers
49 views

I'm looking for a way to add database update scripts to a “playlist” [closed]

I've got a group of scripts to run and I'd like to batch them all together. Rather than write a .cmd/.bat file to run the lot it feels to me like there ought to be something similar to a playlist (e.g....
2
votes
2answers
145 views

Using ORMs in two separate programs which share a DB

I have an application that is divided into two main parts: A web crawler written in Python A REST API written in Golang They share a MySQL database, which is mainly populated/updated by the web ...
2
votes
3answers
80 views

How to properly store messages in a webservice?

We are building a multitenant webservice with PHP and MongoDB. There is a need for communication between service employees and clients, communication can be established in many different ways. Some of ...
7
votes
5answers
753 views

How do I balance 100 clients checking the same database table in a loop?

What I have This is a prototype. I have a pool of 100 clients connected to the server via websockets reporting things and awaiting for commands. The server polls the commands database table of type ...
0
votes
2answers
112 views

Anonymize Public Health Records Database

I am working on a project to anonymize PHI in our health records database when databases are copied to our testing environments. Currently, the database is copied and fields in the tables (first name, ...
2
votes
4answers
255 views

What is the best data model to represent mathematical range (in database, xml, json…)?

For example, greater or equal to 50 and smaller than 100 (>=50 && < 100) smaller than 10 or greater than 40 (<10 || >40) I have been thinking about how to represent ...
1
vote
3answers
257 views

UUID vs Integer

For a database. Should I use an UUID or an integer for the primary key? I will never exceed 2^32 in amount of rows, so an integer should be more than plenty. I would prefer to use an integer, as I ...
0
votes
1answer
137 views

PHP MySQL unique user data [closed]

I'm trying to create a php website that users log into and they can submit data to a MySQL database. How do I make it so the users can only see data they've submitted and not everyone else's data? ...
1
vote
2answers
121 views

anonymous/ghost users in database

This questions is related to this one : anonymous checkout My company bought a CRM software and I am concerned by the way it stores customers' information. The website allows anonymous users to have ...
0
votes
1answer
504 views

Improving performance with caching of files in C# [closed]

EDIT: You were right, the problem is not checking if a file with the same name exists but the connection. I made a mistake measuring the time with StopWatch and included a part which used the network ...
4
votes
0answers
122 views

RDBMS v/s NoSQL for analytics [closed]

While reading about this topic online I have found out that, in general, RDBMS are good for read heavy loads and NoSQL databases are good for write heavy workloads. This statement also seemed true ...
4
votes
2answers
98 views

Storing persistent data that only has a single instance but can change

I'm creating a website CMS in Rails (for fun and learning) and I'm trying to figure out how I'm going to store the data for an "about" page. Due to the nature of this page, the data will only have one ...
1
vote
2answers
673 views

Alternatives to cron jobs or other ways of improving scheduled task performance

I'm working on a social networking website where users gain ratings after specific actions taken and proper conditions are met on the tables. The ratings are calculated depending on 'total hours ...
2
votes
1answer
102 views

Adding new features to software while keeping the same database

My little team is just a few days from launching our web page. Its another user content type page so a lot of our data will be about user preferences and behavior. We decided to get it out of the door ...