The tag has no usage guidance.

learn more… | top users | synonyms

0
votes
1answer
32 views

How should I handle Stored Procedure parameters that need to be the same length as a column?

I'm writing a lot of stored procedures for a SQL Server database that take an id or code that is a CHAR instead of an INT. Most of these haven't changed size in a long time, but when a customer wants ...
1
vote
4answers
100 views

What is a good convention for table key id name?

I'm wondering if there is any good convention for key attributes in relational datatables. ID or Id seems to be good. however it conflict with others ids when joining. MyTableNameId does not ...
2
votes
0answers
24 views

Specifying a bi-directional unique constraint on a join table in Postgres

Problem description I have a situation where I want to enforce that a relationship can only be defined once and I want to solve it on database (Postgres) level. The following situation I consider ...
4
votes
2answers
175 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 ...
1
vote
1answer
40 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
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
3answers
118 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
129 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
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 ...
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
441 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
126 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
81 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
99 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
127 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
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 ...
-1
votes
1answer
60 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
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 ...
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, ...
2
votes
1answer
190 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
340 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
63 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
269 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
793 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
371 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
88 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
248 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
458 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
153 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
312 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
380 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
839 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 ...