The relational-database tag has no usage guidance.
4
votes
2answers
166 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
120 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 ...
1
vote
1answer
37 views
ERD for a Lab Database
Question: In a database for a clinical reference lab a specimen, when taken, triggers a test order (for testing obviously) creation.If that test order produces a failure, the specimen is given a new ...
2
votes
1answer
62 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
3answers
114 views
How can one think of a relational schema in terms of storing it in a NoSQL database?
Recently I've been wanting to experiment with NoSQL databases, especially document store ones. After reading, I still don't understand how one might model information that is contained in a relational ...
1
vote
2answers
121 views
Microservices with the same database
I have a monolith application on a server and I want to break it into multiple microservices.
Because of that for the moment I have to use the same database for all the microservices and each ...
58
votes
7answers
6k views
Why does the relational model for a database matter?
I am approaching a project where I'll be having to implement a database with my boss; we're a very small start up so the work environment is deeply personal.
He had given me one of the company ...
1
vote
1answer
59 views
How to associate similar data with different tables in a database?
My application has users and ads. A user has a unique email. An ad belongs to a user (so, the ads table has a user_id FK, that is simple). Every ad can also have multiple emails and phone numbers ...
3
votes
1answer
98 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 ...
0
votes
5answers
131 views
Database design - When should a table column actually be a table?
Compare Database #1 and Database #2 below
DATABASE #1
POSTS---------------------------------
|___Id________Category_Id______Body__|
| 1 "For Sale" "...." |
| 2 "Requests" ...
1
vote
2answers
102 views
Are there downsides to not explicitly defining database relationships?
I have a PHP application with a MySQL database. I have not defined any formal relationships between tables. (eg. Set Foreign key, etc). Although in every query (CRUD) I always consider the relation ...
0
votes
0answers
29 views
Should I use weak entity instead of regular to describe my kind of components?
First time trying to make a E/R model of my database.
Trying to understand weak entities and regular entities.
Should I make the CPU, RAM, Mainboard, etc. entities weak instead of regular?
17
votes
3answers
436 views
Relational databases and iterative development
In many approaches to software development like agile methodologies, Domain-Driven Design and Object Oriented Analysis and Design, we are encouraged to take one iterative approach to development.
So ...
6
votes
3answers
121 views
Storing hours a merchant is open in postgres
In postgres, I need to store the hours a merchant is open each week. Essentially, the information you'd see posted on a sign in the merchant's door:
Mondays: Closed
Tuesday-Friday: 9am-1pm, 2pm-6pm
...
3
votes
1answer
80 views
Correctness of ERD diagrams
I am new to database modeling.Here is a problem I was trying to solve:
Information about movies,stars and studios.Movies are shot in studios that own them.A movie is shot in only one studio.Stars are ...
2
votes
0answers
95 views
Building a generalized notification system: passive vs. active
Recently, I've tried my hand at building a notification system, but I quickly found that notifications are tricky things, especially in the context of building a general model. The diversity of what ...
5
votes
2answers
91 views
Junction table related to another junction table
I'm defining a DB structure, and I have a strong feeling I'm not doing it right. I have hotels that can be configured to offer some optional services (airport pickup, massage), that can in turn be ...
42
votes
8answers
6k views
Is the use of NoSQL Databases impractical for large datasets where you need to search by content?
I've been learning about NoSQL Databases for a week now.
I really understand the advantages of NoSQL Databases and the many use cases they are great for.
But often people write their articles as if ...
3
votes
3answers
125 views
Relational table design for data ownership
Setup
(schema simplified for clarity)
Suppose I have a table called resource:
+---------------+--------------+------+-----+
| Field | Type | Null | Key |
+---------------+-----------...
5
votes
2answers
185 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 ...
-1
votes
1answer
59 views
Is it a good idea to store DSL for rules in a database? [closed]
In the (shop-)system I'm working on we have promotions and voucher-codes, which, when you redeem them with an order, give you a certain amount of bonus points.
The codes are either your typical ...
1
vote
1answer
260 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 ...
3
votes
2answers
203 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, ...
2
votes
1answer
187 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), ...
3
votes
1answer
72 views
Two tables with similar structure and referential integrity or one table
Overview
My question revolves around balancing database and code simplicity using 2 options to design around the following requirement.
Background Facts
The DB: Sql Server
The Code: ASP.NET MVC C#...
-1
votes
1answer
80 views
Relational Model to NoSQL model
I'm trying to switch my brain in to a NoSQL mode (document oriented database). I created a JSON datastructure that looks a little bit like this
{
"listInstances" : [
{
"listClassId" : 1,
...
1
vote
1answer
43 views
Creating a specific table for shared columns among other tables (models)?
I'm creating a webapp that has a variety of models: Product, Category, ProductOption, etc.
All of these models have a set of shared attributes: name, description, image, etc.
There are two options I ...
-2
votes
1answer
90 views
How to design database to keep list of special members among all members? [closed]
I want to design Database which works as below explained.
We have some members and they are listed in many categories.
Among members, some have special privileges.
We have Memeber Table
We have ...
0
votes
1answer
327 views
Storing Multi-select and Single-select data together
I've got a database with some multi-select fields. To keep it simple, let's just say those fields are MatchGender and Interests. I want users to be able to find people who match (hence Gender is a ...
0
votes
1answer
62 views
How is called the reciprocal operation of the join on two tables
Consider two tables in a relational database
1|A
2|B
1|X
1|Y
2|Z
whose join on the first column is (if we drop the column used for the join)
A|X
A|Y
B|Z
How is called the converse operation ...
1
vote
1answer
53 views
Read complex data from a relational database through a join or through concurrent requests?
Consider a simple relational database with two tables:
item_index: item_id|item_name
item_property: item_id|property_name
Each item has several properties, and I want to iterate over the collection ...
0
votes
2answers
267 views
Can relational database tables always be topologically sorted by their FK reference relation?
Suppose I have some conceptual, not physical, E-R diagram describing a relational database. I want to sort all the tables in the database using a topological sort, where the partial ordering between ...
4
votes
3answers
779 views
How does Polyglot Persistence handle relational data?
I have recently been studying up on microservices, and an associated idea that I've seen is that of polyglot persistence and microservices working with their own databases, or whatever storage they ...
1
vote
1answer
369 views
Am I planning my database/backend correctly?
I'm an iOS developer and to be honest I'm not really looking to become a database expert right at this moment, but I do need to know how to properly plan out what I believe to be a pretty cookie ...
0
votes
2answers
147 views
Getting to know my way around a database with hundreds of tables [duplicate]
I'm new to a small company that's pretty much run by one guy. He knows all the ins and outs of the system which is quite large. We have a database with about 300 different tables. I'm struggling to ...
0
votes
1answer
91 views
Reproducing a bug in production that happens with a large database
We are running Oracle 12c and a custom application written in Java/JDBC (Java 8). Our production system is running a 5TB database and runs into deadlocks. However we have been unable to reproduce this ...
0
votes
1answer
87 views
Is there an established pattern for associating a database entry with a Java class that executes on that data?
I have a behavior tree with different leaf nodes that execute tasks. When building out the tree relationship in the database I need an association between the leaf node entry and the Java class that ...
2
votes
0answers
244 views
Is this a good implementation of mongodb for a money management tool?
I have a money management tool which has users. Each user may have several accounts/funds (e.g. bank account). For each account/fund there may be thousands of transactions.
Users may only view their ...
0
votes
3answers
457 views
What is the hierarchical DB schema for this xml?
As per this link, this is the xml that maintains "BookStore" data.
I see that number of occurences of author are varying for any book.
xml's hierarchical representation of BookStore looks more ...
7
votes
3answers
232 views
How to model and store an entity which has “one or more” values
I'll use C# to design the model to be persisted
Let's suppose I have the following entity:
public class Episode {
public int Id { get; set; }
public string Title { get; set; }
public ...
4
votes
2answers
152 views
Using audit trail as time machine?
I would like to do
SELECT * TABLE t (using data from 1st of march 2012)
I already have a nice audit trail of all tables in the database. It basically makes a copy of all rows that change, storing ...
2
votes
1answer
306 views
In a Table Data Gateway, where do I put queries that use joins
The book Pattern of Enterprise Application Architecture has the following description over the Table Data Gateway pattern:
“A Table Data Gateway holds all the SQL for accessing a single table or ...
2
votes
2answers
372 views
Alternative locking strategies
I'm having a problem with the design of my application, that neither optimistic nor pessimistic locking tends to solve. Here is a simplified/altered version of the problem that describes the situation....
4
votes
3answers
224 views
How to track temporal changes in a database?
Imagine a software for theater owners. You can accept reservations for the different cinema halls. Each hall has a different number of seats.
Say the owner wants to know how many people visited the ...
3
votes
2answers
398 views
Should I use relational or noSQL databases for this problem
I'm working on a project where one part is to save data of different people. A typical person might have the following structure:
Email: Multiple values possible
City: Multiple values possible
...
2
votes
1answer
831 views
How to represent an optional-to-optional relationship in a database
I have two types of users: a CRM user and a system user. We have a record of every CRM user, thanks to a nightly sync that we run (so we don't have to hit the CRM API every time). We have system users ...
1
vote
1answer
63 views
Looking for advice for the following MS SQL Schema
We currently have the following SQL Schema:
Projects
Budgets
BudgetTabs
BudgetSections
BudgetTasks
BudgetTaskDetails
...
0
votes
1answer
335 views
RDBMS (MySQL) logic question. How to join multiple “FRIENDS”
so this is a logic question as to creating the proper relationships in a MySQL database.
What logic would you choose for the following scenario?
This is a back-end for an app where people can have ...
5
votes
2answers
296 views
Database Normalisation - stuck with “categories”
Context:
I'm working on a database of museums and Categories will be one way the user will be able to search. e.g click on "Music" and a list of music related museums turn up. Pretty simple.
Problem:...
0
votes
1answer
104 views
is there anything called logical schema and the conceptual schema of a database table?
i want to know whether there is anything called logical schema and the conceptual schema of database table. if so what is it.i searched lot of places for this specific two words, but did not find ...
