Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Join us in building a kind, collaborative learning community via our updated Code of Conduct.

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

-1
votes
0answers
78 views

Can data insertion be made to two related tables at once? [on hold]

I've created two tables named: evaluation_details and visits. Users can make an evaluation within the website to any department they want and while doing so, they can put date and the time they ...
0
votes
1answer
71 views

Express unique recursive task system

I would like to express a task as either itself or a container for other tasks (recursively). The problem is that each task has to be one of two fundamental types: a goal or a routine. And, children ...
1
vote
1answer
49 views

How to organize users data in NoSQL database for a user invite feature (even before a user sign up)?

I am doing a collaborative note editing app which uses a NoSQL database (Firebase) in server side. Each user can sign up using their phone number or email. Currently, the user object has only four ...
0
votes
1answer
27 views

Extending photo album app with videos

I have a personal photo album application where I upload my photos. However, I'd now like to extend it with videos as well, and I'm unsure how to properly perform this extension. The current design ...
1
vote
1answer
53 views

How to create a db schema for “if this then… elseif … elseif …” and so on?

I want to create an app where a user will go through a series of steps every day. All the subsequent step will depend on the previous ones, that is, 2 users can have different experiences depending on ...
2
votes
3answers
113 views

Logical separation of database content

Background I have an application which stores a lot of entities in a classical relational database (Microsoft SQL Server) and I use an ORM (Entity Framework) to query data from it. This database has ...
-3
votes
0answers
57 views

How should I handle a change of prices of an order?

Say, I have a site-shop where some products are sold. Each product has the price and the price can change. A purchase can also include shipping, discounts, taxes, etc. And they all are subject to ...
-2
votes
0answers
49 views

What data structure for “Include from parent”, “Exclude from parent” and “Add at this level”?

I have a business system that has a hierarchy of objects that may or may not inherit data items from their parents. As a "example think of it this way: Top level = Trunk Second Level = Branch Third ...
0
votes
0answers
17 views

How to implement dynamic settings in a web app — db table design?

In my web app I want to be able to use Settings as an admin that I can edit/remove/add via my admin page I think I'll create a table like this: settings(id: int, key: string, value: string) I ...
1
vote
2answers
347 views

Why should I use foreign keys in database? [closed]

In my 10+ years of experience in the IT field, I have never used foreign keys in any of my project and I never felt the need. I did work with professional databases which had foreign keys constraints. ...
0
votes
0answers
102 views

Ontology optimization

I have a pretty complex ontology of an application which is used for decision making. By ontology here, I mean basically how entities are related and how they are composed and structured (the context ...
1
vote
1answer
67 views

System design for credit calculation usage

We are implementing a system where a user can purchase 'credits' to be spent on two items. I am hoping for suggestions on how to implement such a system. We have two ways of spending credits: ItemA ...
-1
votes
0answers
42 views

Database table that would lot of data

What would the best aproach be for table that contains lot of data? Let me demonstrate the example: Imagine you have website, and you track position of mouse of every logged user, so you would have ...
0
votes
0answers
36 views

Polling Postgres DB architecture

We are using Spring Integration DB polling which polls a table every second and executes some logic based on result. My questions are: What are some good practices we can implement to account for DB ...
0
votes
0answers
21 views

Implementing Products, Purchases and Payments and Statuses

I have Products, Purchases and Payments. I'm trying to figure out how to deal with Payments and statuses and the ultimate status of Purchase - "paid", "failed", "refunded", etc.... My requirements: ...
2
votes
3answers
184 views

Database design for generic behaviour

I have a few tables that are generic. One of them allows a user to comment on something, and the other one allows a user to favorite something. Initially, I had thought that to make it possible to ...
-1
votes
2answers
66 views

Should the TotalGrandPrice be property or Order or Invoice

I'm developing a shop. I'm trying to figure out: where should "total_grand_price" belong to? "total_grand_price" = price of all order items - discount + shipping + taxex +- etc.... Should ...
4
votes
2answers
302 views

Need to email customers once per year - best way to record this

We need to email our customers once per year (yes it's opt-in etc). As I see it we have 2 ways to record the fact we contacted them (so we know not to contact them for another year). A log, or a '...
1
vote
0answers
47 views

Database design for a subscription service transactions

I need to create a table or a set of tables that needs to house transactional data of a subscription-based service, so that certain metrics can be queried efficiently paired with necessary post-...
1
vote
1answer
74 views

What are good ways to encode order between records of a relational table

For example, when an issue tracker offers the functionality to users to keep a prioritized backlog of tasks and used a relational database for persisting this information, what are effective ways to ...
0
votes
2answers
104 views

am i asking for trouble? DB & dealing with variable item records

I feel this is so basic, but it just seems too easy and obvious and why didn't someone suggest this 10 years ago. And I know there a lot of much brighter people than me in the software dev business......
1
vote
1answer
66 views

How to store orderable items of different type

I've got 'Lists' that contain different objects sometimes with similar class, sometimes with different class. So I have a table 'lists' to store a list. I have a table 'strange_objects' to store a ...
2
votes
2answers
69 views

Is ER model used in database design and software design the same thing?

ER model appears in both database design and software design. I was wondering if ER models in both designs are the same concept? If not, what are their differences? Thanks.
1
vote
1answer
70 views

In the context of nested database relations (A->B->C), what are the considerations for having an additional “shortcut” foreign key (A->C)?

I have a normalized relational database. I often find I need traverse multiple tables to get the related object of a related object and so forth. So by that I mean let's say I have related tables A->...
0
votes
2answers
182 views

Should I use NoSQL (MongoDB) or SQL (postgresql) database for my application?

I am planning to build a live audience interaction webapp similar to Slido as practice/fun project. User groups: admins and anonymous users Admins: login create event (define event period and ...
0
votes
1answer
38 views

Designing an efficient search by tag solution

I've a project where the users will be able to tags themselves with skills tags (maybe predefined in the system) , and then the project managers on a company would be able to search the best user with ...
1
vote
0answers
46 views

Multiple version of database content

I am working on schedule module of a project management software, every project has two types of schedules. Base Schedule Actual Schedule The actual schedule is a copy of base schedule which is ...
0
votes
1answer
40 views

Implement Customizing records

I have the following tables: Organization. Articles Templates Organization is the parent of both articles and templates. On the articles index page, I display (by default) all templates for the ...
0
votes
1answer
49 views

Database design: where to store a “current” or “selected” flag in a parent-child hierarchy [duplicate]

Sorry, maybe it's a well known design principle, but because I don't know a name for this kind of relation, I was not able to find any existing answer. So, let's say we have a parent which may have ...
0
votes
3answers
102 views

Database Auditing: “Updated At” Null or Not Null?

Currently, we've been working on the architecture team on defining the database models. What has been troubling me is my superior's advice when it comes to working with audit fields. He advocates for ...
0
votes
2answers
183 views

JSON in Database Schema Design

I'm working on a web application with constantly changing requirements. The relationships between entities in the database have mostly remained the same but the attributes of each entity type changes ...
0
votes
0answers
135 views

Use of UUIDs (Primary key) in a REST API

I faced the problem bellow when I designed our new app. We are thinking to have one extra column for some tables which is a UUID for security purposes. So, there are two different solutions. First ...
3
votes
2answers
89 views

Time series and unique users

I need to collect user data every few seconds where each user is unique. I've done some research and concluded that I should consider my data as time series data because I'm looking at how user ...
2
votes
3answers
154 views

Deal with Enumerations (Lookup tables)

I am trying to design the DB schema of my new project and I need some suggestions. I decided to create a lookup table in order to save the enumeration values. So I have a table Status with these ...
3
votes
5answers
349 views

Use UUID on top of auto increment ID

I am trying to design the DB schema of my new project and I need some suggestions. I am thinking to have one extra column for some tables which is a UUID. I wanted mainly for tables which contain data ...
-1
votes
1answer
64 views

Separate tables vs single table for users registered through social

In a website users can register through different social logins ex: facebook, google, github etc in addition to normal email registration. There is one users table to store these users, and in ...
0
votes
0answers
25 views

Techniques for guiding users to correctly tag items?

I'm working on implementing a user defined tag system for my marketplace application's search system. Items can be anything so I can't know before hand what the tags will be, but I need to create ...
6
votes
4answers
348 views

is it okay to use negative numbers to extend an industry standard data model

I'm working on a project with client that needs to rely on a proprietary data set. They also have custom data that logically fits alongside the proprietary data. What they do is use the same tables ...
1
vote
1answer
40 views

Flexable Table, or multiple tables?

In my application, I have many things that use images in different ways. For example, a product may have a logo and many screenshots, a user can have a profile image, and a company can have a logo. I ...
1
vote
2answers
166 views

Do you preferer null or default values when forced to, for meaningful necessary fields?

Let's consider a scenario in which a user registers to a certain service which needs his gender, birthday and location to function properly. Sometimes we want to try registration with just email then ...
3
votes
1answer
89 views

What is the better search system, user defined tags or pre defined attribute fields?

I'm developing a marketplace application that will eventually list a large number of different types of items. Currently our search system (powered by Algolia) is built on pre defined attribute ...
2
votes
1answer
39 views

Architecture and Database re-design for migration and support of multiple clients

I'm evaluating the migration of the following application's architecture: - Nginx + PHP + MySQL - Currently the infrastructure is scalable and redundant in the AWS cloud and It was designed to support ...
0
votes
1answer
88 views

How to make an server side application independent of database

We have a server-side application (in JAVA, communicates via web services) that accesses a database. If we wish to sell just the application to a customer, so that he can use his own choice of ...
2
votes
4answers
288 views

Designing database for a table with huge number of rows

Suppose, I am developing an application to keep track of daily sales of of a small retail shop of only three employees. Suppose, the owner agrees to buy only one PC, and, refuses to purchase a copy ...
4
votes
2answers
201 views

How to properly use a database containing common tables in a multi-application system?

I'm trying to determine the best application and database architecture for a project I am working on. The idea is that there will be multiple, separate applications that will all share a few tables ...
1
vote
1answer
60 views

Table design with Microsoft SQL Server

I was hoping to get some advice from you folks on my table design methodologies (specifically in MS SQL if that matters). Let me start by showing a couple of example tables that I might have in a ...
-2
votes
1answer
67 views

Correct terminology for a table which provides “dynamic columns”

A common pattern in database design is to append dynamic columns to a table with a design like so: create table MasterTable ( Id int not null primary key , Name nvarchar(64) not null unique ...
1
vote
1answer
148 views

Handling guest users in ecommerce application

I'm currently developing an Ecommerce application and am trying to figure out how best to add a 'guest checkout' feature. The 3 main models that I am wrestling with to add this feature are Address, ...
0
votes
3answers
159 views

How to associate one type with another in multiple contexts?

An application has types Game (and other business units) and Message. (Message is not a simple class with text, but a composition of a variable number of blocks like texts, images, and videos). ...
3
votes
1answer
75 views

Design for backup tables

We have 11 tables for transactional data. Now, this data may have some data integrity issues. We have prepared a set of SQL scripts to fix the data integrity issues, which customer can execute from UI ...