For questions about structuring the data within a database. How to lay out tables, whether to use a relational DB or not, etc.

learn more… | top users | synonyms

1
vote
1answer
226 views

Best way to structure many-to-one allocation of users to projects in NoSQL

I apologise if this is a bit of a stupid or broad question, but I'd like some general feedback please! Say I have this DB structure for users (in Mongo/JS, but really it's all pseudocode- and the IDs ...
4
votes
2answers
115 views

Micro-service database management?

Most people say when building micro-services, that it's best to have separate databases for each of the microservices. E.g: User-microservice: DB - User userId name Etc... Review-microservice:...
3
votes
2answers
95 views

Building an abstraction database

The Scenario I have a Classic ASP project with a SQL Server backend that I need to port to ASP.NET MVC. The legacy site makes liberal use of schema definition analysis in the application itself. ...
1
vote
1answer
53 views

Database Object That Reads From Two Tables and Writes to One

I am updating a database design that must be compatible with old and new applications. My goal is to make a design change which minimizes impact on the existing code. The Design Change Currently our ...
0
votes
1answer
37 views

DB Data structure for .Net sequenced list with insert, delete, move

The problem is maintaining in .net a list of rows that have a specific sequence where rows can be inserted, moved and deleted, and being able to save and restore that sequence from disk. In memory ...
0
votes
2answers
63 views

Should we sanitise and clean data or only display it in the expected format?

So my problem is, I have users entering their data(names, address) in a mixed form- some with first letter in caps and some without, some inserting more than necessary spaces and so forth. Also, ...
0
votes
1answer
86 views

Row level security with multiple rules

I need some assistance with designing a system which implements row-level security that could have multiple conditions that need to be satisfied. Context Suppose I have a user. A user can belong to ...
0
votes
1answer
43 views

designing a data warehouse: action or status based

I am designing a data warehouse for a sales platform. There exists a group of users that offers and group of users that demands entities. The production data base holds two sources of information: ...
1
vote
1answer
63 views

How to model the database tables for calculating on peak and off peak usage data

I am using MS Sql Server and my system contains power usage data in one table as timestamp and values for KW(two columns). This data is collected at 1 hour interval. At the end of the month I need to ...
4
votes
6answers
10k views

Better way of storing key-value pairs in the database?

I have a C#/SQL Server program that sometimes needs to store data. The data could be a response from a web service, a result of a database query, or any number of other things. There's no way of ...
0
votes
0answers
36 views

database architecture for ecommerce app

I’m creating an app that allows users to post, sell, and purchase items. Every item has a count integer attribute that is set by the initial user and which is reduced by n with each purchase. When ...
0
votes
4answers
49 views

Including record data in the ID number

I'm working on a project that requires assigning ID numbers to records about people in a database. Each person may have many records associated with their ID number. There was a discussion about ...
4
votes
2answers
176 views

Is it okay to use a non-ID column for foreign key reference?

I have my tables as below Role(Role ID, Name, Other_Columns) Command(Command ID, Name, Other_Columns) I have association table RoleCommand. Is it okay to have RoleCommand(RoleName, CommandName) ...
2
votes
3answers
123 views

How to simplify this Role and Permission management design?

I'm working on a web application that has users with multiple roles and each user can perform multiple operations, based on user's role, and the permission level the role has on the operation. I came ...
0
votes
4answers
84 views

Should database tables be separated by concern?

My question is related to when should a database table be broken into multiple tables with relations? However, I have a clearer one-to-one relationship than in that question. If I was designing the ...
0
votes
1answer
56 views

The best approach to make a report based in dynamic data

I have an Asp.Net MVC application where I need to create a report. This report will show some data about an entity (activities), but only activities of ONE specific type that will be inserted in the ...
1
vote
2answers
51 views

Creating a web application with full text search on dynamic data

Even after thorough requirements engineering we end up with users wanting to attach 'notes' to their otherwise well-structured data records, in other words: arbitrary key-value pairs. Their primary ...
2
votes
2answers
98 views

Is a one-to-none relationship one-to-one or one-to-many?

I am working on an existing database design. There is one table in which each row is created with only half the columns populated (the remainder are initially all NULL, except for an is_populated ...
2
votes
2answers
60 views

Use one query that fetches redundant data from linked tables or multiple queries and join them in code?

I have three tables with a chain of two one-to-many relationships. There are two ways I can think of of fetching the data based on the primary key of the first table. JOIN each table (or LEFT JOIN ...
4
votes
2answers
103 views

Is my database supposed to contain the links to the files, or would it contain the files themselves?

I'm trying to better understand how to use a database in a personal website. I have a set of lists, like my shopping list, to-do list etc. Currently, they are all stored in text files with hard-...
2
votes
1answer
61 views

Design activities / events logging (ex: fields update) in RDBMS

I am trying to implement a activity tracking system in Ruby on Rails for an API only application. I have a table named letter with information as follow : id (primary key) sender_id (foreign_key on ...
2
votes
0answers
82 views

Logging Database Changes

What is the proper way of storing a changelog, similar to git, for data in a database. Currently, I have it set up so that every line in a table is given a unique string id that is used to ...
29
votes
5answers
12k views

Configuration data: single-row table vs. name-value-pair table

Let's say you write an application that can be configured by the user. For storing this "configuration data" into a database, two patterns are commonly used. The single-row table CompanyName | ...
5
votes
2answers
69 views

How to add users to a mapping table?

In the accepted answer to "Encrypting stored data for multiple unique users to access information", @lserni describes a mapping table for users. But among the operations for the table, they didn't ...
4
votes
1answer
50 views

Reproducible uniqueness

So, I have a bunch of calls that are all being generated with a UUID1 throughout the day. At the end of the call the call is processed and some metrics around that call are generated and stored in ...
1
vote
1answer
244 views

how best structure localization of content type category name

I'm working on swift / iOS with a parse.com backend but this question could be applied to any language and is a general conceptual question. I'll use a specific example for illustration purpose. I ...
0
votes
1answer
47 views

Use redundant data in database design when have multiple relation

In my web application users can submit their advertises and buy package for advertise (packages are used for priority of showing advertises) Database schema is like this: **Package: id, title, ...
7
votes
2answers
2k views

Database Schema for Pricing Products (packages, promotions, qty based, limited time offer…)

I'm working on a new point of sale for a company that products for different prices depending on the product mix. All products have a base price. To explain my problem, I will use the following info:...
1
vote
0answers
60 views

Saving tabular, spreadsheet-like data in a relational database?

I'm hoping this question isn't too broad or too prone to opinionated answers, since I could really use some pointers. I'm trying to come up with a way to persist tabular, spreadsheet-like1, data in a ...
6
votes
4answers
240 views

Is it too verbose to always have 'modified' time stamps on all entities within a database?

The question can be summarized as: In a database (regardless of type), would it be considered a good practice to always include updated, created (and possibly deleted) properties, for all entities,...
3
votes
1answer
99 views

Getting posts from users that are being followed: two different implementations

I have two different implementations for retrieving posts from the users someone is following and I want to know which one you would recommend using. First I'l show you the tables, and then the two ...
1
vote
1answer
274 views

Best Multiple tables product with categories

I was looking to create an EAV structure for an inventory management system. But I read this answer and decided against it. I am looking to create multiple tables for all my products. But the trouble ...
6
votes
5answers
131 views

DB design: what is a good way to handle properties for game?

I must persist handful of properties for several games in relational DB. No other type of DB can be used. Number of games will be around 4-5, and number of properties is (for now) kind of small, but ...
0
votes
1answer
44 views

User, user Role and user Info, data base design problem

I am creating an application that will manage job interviews and help the employers to choose the job seekers based on a ranking system that's based on some user info. I've created a database that has ...
1
vote
1answer
105 views

A large Oracle database with no foreign keys [closed]

I am struggling to understand the design of an Oracle database that I have been working with for the past 9 months. The database is for our business management system and I work with it mostly for ...
2
votes
1answer
71 views

Designing Storage for Different Time Systems

I've been considering doing some kind of application that allows users to track events in mythical settings. However, not all mythical settings allow for normal times. What if one user wants star ...
0
votes
0answers
36 views

Design a System to save a data that will be missed from another system

I'm building a middleware system where I'm saving the data from VERY slow API, The system behind this API works like this It produce a data blocks, lets denote a datablock by A. While A is used by ...
0
votes
0answers
24 views

What is good practice when saving textual data which contains meta information?

This is bugging me for quite a while and even though I decided to do it in a certain way I am still no convinced that I decided well. Here is the background to it: One of the core features of my ...
1
vote
1answer
95 views

What Graph Theory approaches or algorithms are useful for designing a House?

I have a set of data about a family, and I want the software to find a house design that fits the data (which includes stuff like preferences and whether they want a pool. The current way I am going ...
0
votes
2answers
123 views

Create a separate table for employees work hours?

I have a table with employees information: id, active, first_name, last_name, gender, id_number, address, picture, password, comment Every employee has an entrance time and an exit time for each ...
0
votes
0answers
54 views

Which is the best way to manage multi-account multi-user web application?

I am junior web developer, currently I am developing school application with PHP and MYSQL. After finishing the app I would like to distribute to more schools by allowing them to register in the app ...
1
vote
3answers
217 views

How to maintain user relationship when user un-follows another user

I have a table of user relationship which stores who follows whom, the table structure is as follows -id -follower -following when someone follows any other user, I add a record with follower user ...
2
votes
1answer
63 views

Best approach to store huge amount of denormalized/dynamic data with relational database

Here I have a very basic ILS (Integrated Library System) done a long time ago (made with C#/SQL Server), in production for years. But now I have a demand of make this system MARC 21 standard compliant,...
1
vote
2answers
71 views

Conditionally show questions based on previous answers in a database driven survey

I'm trying to design survey-type application that uses user responses to determine what the next question should be. For simplicity's sake, question types can be either combo boxes (they have a set ...
2
votes
1answer
570 views

Designing persistence schema for BigTable on AppEngine

I have tried to design the datastore schema for a very small application. That schema would have been very simple, if not trivial, using a relational database with foreign keys, many-to-many relations,...
1
vote
1answer
176 views

Google Datastore and deep records

I'm in the beginning of creating a website that uses Google Datastore to store information related to clients and I'm curious if the record is too deep for NoSQL or if I'm fine. Essentially I'll be ...
10
votes
3answers
416 views

Which data store is best for my scenario?

I'm working on an application that involves very high execution of update / select queries in the database. I have a base table (A) which will have about 500 records for an entity for a day. And for ...
14
votes
6answers
42k views

what do database programmers do?

Every time I read about Oracle programmers etc, I get confused. I don't know what exactly they do. From my understanding, application programmers need to develop the core functionality. The libraries ...
4
votes
2answers
105 views

Database design, processor vs inconsistency

I was wondering, if I have a table that stores, lets say, a user's transactions, then when I need to fetch the user's current balance I would have to go and use SUM() like SELECT SUM(value) FROM ...
9
votes
3answers
324 views

How to share a code base among 10+ projects and minimize pain?

I have a number of applications that share the same data in the same database. To try and minimize redundancy of code the Data Access Layer is a shared project. This prevents each project from needing ...