Questions tagged [database-design]

The development of the conceptual schema and/or the logical model and/or the physical settings of a database.

Filter by
Sorted by
Tagged with
-1
votes
0answers
18 views

Storing past appointments, orders and invoices as jsonb [on hold]

Will that be wise idea to store past appointments orders, reports and invoices in a postgres's json b format. Idea is, once these entities are excuted user can't change the content other than voiding ...
0
votes
0answers
20 views

Modelling dynamic data types in relational database [on hold]

I'm about to start work on a fresh project where we want to allow users to create new fields with one of multiple data types and attach those fields to items in the system. This is to make the system ...
0
votes
2answers
47 views

SQL rounding last zeros

I need to store the exact value of a decimal: For example 0.00500 and 0.0050. If I use decimal (10, 5), I receive 0.00500 every time. If I enter 0.0050, I still get 0.00500
1
vote
0answers
23 views

Design issue in transitive dependencies between tables

This is a made-up scenario, but I imagine that it can be of general interest, so I'll post it here. Imagine the following business rule: A course has a grade scale that determines what grades a ...
1
vote
0answers
26 views

Does this have a name?

Does this have a name? I dont really know how to describe the situation, and furthermore, is this a desing-flaw? T4 has a FK to T3, and to T1, but I can get to T1 by T3. The T1, T2 refers to MUST ...
0
votes
1answer
29 views

Design a table of items with many features; how to group those features

I have this table Storeswhich represents stores. Those stores are managed for many types of persons, like Administrators, Managers, Supervisors and so on, i.e. Each store has an administrator, a ...
0
votes
0answers
15 views

Designing database with filter tables

I am tasked with the redesign of an old database that is not performing very efficiently. Now I am no where near an expierenced database designer, so I am hoping you guys can help me figure out some ...
0
votes
0answers
12 views

Proper way to reverse/correct transaction [on hold]

Let say that I have transaction table as below. Transaction ------------------ TransactionID UserIDFrom UserIDTo Amount TransactionDateTime Scenario 1: This transaction need to be reverse. Solution: ...
0
votes
2answers
71 views

Proper way to add new column in database

What is the proper approach when we add a new column into an existing table? For example, I already have columns like, Foo1, Foo2, Bar1, Bar2. Now I want to add a new column called Foo3. What is ...
1
vote
2answers
332 views

Database Table/Column rules [on hold]

We are making a new database design where we are setting some rules upfront. I dunno how much it makes sense to have these rules in place, Each table and column name should be meaningful. It should ...
1
vote
1answer
33 views

Extending fiat transaction log to support cryptocurrency

Let's assuming that I have an existing database that retaings fiat currency transaction as described below with decimal (18, 4). Now, I would like to record bitcoin, ethereum and erc20 token ...
-1
votes
0answers
24 views

Multiple live identical database [on hold]

I would like to implement this in a digital records management system. Whenever a client access a document, the document will be fetched from the all the live database and be compared to each other ...
-1
votes
2answers
31 views

Calculate value with multiple condition and How to design database [closed]

Sorry i can't type table format in post thus i have attached image file in this post For example if employee do OT on working_day > 3 Hrs.they will get rate 18 (MAX 18 for working_day) if ...
-5
votes
0answers
11 views

Create Document Register in Database formatt [closed]

One of the roles in my employer is to manage document control between multiple contractors to the site users. We do this storing info
0
votes
2answers
32 views

Monetary value in integer over decimal datatype [duplicate]

I have came across several articles that mentioned that advise on not to store monetary value as decimal, but use integer instead. The reasoning that it does not store actual value and will cause some ...
5
votes
1answer
161 views

Double entry accounting schema design

We are implementing a software that allows to send "virtual" money between user internally. We have started our database with a single entry as follow (simplified table). We only one record one ...
-2
votes
0answers
43 views

How do you objectively measure the effectiveness of a data model? [closed]

Not sure which topic this belongs under, but I'm doing research on the theory behind the following questions: What is the best data model for a particular data set (let's say this data set is large, ...
-1
votes
1answer
40 views

Is this Database Model for university is correct [closed]

The education system in our country is a little bit different from other countries. We take subjects all through the year and finish the year with the same subject. We have 4 main exams through the ...
0
votes
0answers
13 views

Design: Maintaining Common Database Entries for Different Users

Scenario We have a requirement that presents some items for the users that they can add to their 'shopping' cart. There are some common items between users, but users can add / edit the names of the ...
0
votes
1answer
27 views

ER Diagram Question [duplicate]

I am reading a book about database systems and in the chapter covering database design using ER, the author aims to explain when to use aggregation versus ternary relationship. In short, each Project ...
1
vote
0answers
41 views

Linguistic corpus design: order elements of different types

I'm trying to design a database for a linguistic corpus search engine. Basically there is a manuscript which contains several songs, and every song in turn contains stanzas and proses. Since stanzas (...
0
votes
1answer
23 views

Database Architecture Identifiers

I am kind of new around DBA world, i am currently working with JMeter(front/back end performance testing) but when i have spare time (which is a lot lately..) i try to learn about DBA Development, ...
1
vote
1answer
27 views

Understanding many-to-many ternary relationship sets

I am new to entity-relationship diagrams. I'm a bit confused when it comes to interpreting ternary relationship sets such as this: Does this signify that every relationship instance of Party will ...
0
votes
0answers
23 views

DynamoDB architecture/design review [closed]

I'm trying to develop an app using AWS AppSync and a single DynamoDB table as datasource. I'm thinking of designing the model like so: Relationships will be stored as attributes on each row where ...
2
votes
0answers
26 views

Aggregation Vs Ternary relationship

I am reading a book about database systems and in the chapter covering database design using ER, the author aims to explain when to use aggregation versus ternary relationship. In short, each Project ...
0
votes
1answer
35 views

Is it possible to reference multiple foreign keys with one primary key?

Really hope this is the right place to ask this, but I just want to make sure this is modelled correctly. I'm trying to create a relationship between store ownership (i.e., stores that bought other ...
2
votes
1answer
23 views

Creating a view (or table) from JSON data with two different shapes

I'm pulling in JSON data from two different sources that have roughly the same information, but in different shapes. Let's say I'm pulling in data about music albums, and one of the API responses ...
1
vote
0answers
38 views

Room bookings database - does it make sense to create two tables instead of one column?

There are two types of entities: user and room. A user can book room select room unbook room unselect room "Selected room" is a state that indicates when a user has decided to book a room and so he/...
2
votes
2answers
47 views

How to Represent Equation with Variables in Relational Database

I am building an application to store the different formulas used for different subjects in school, and I have drawn the following database diagram to depict that scenario: I am having trouble ...
0
votes
1answer
21 views

Database design for stores with and without branches

I am building a database with primarily these two kind of stores: a) Simple small family stores b) Big and many branches stores Those two kinds of stores are represented in a Stores table; with the ...
-2
votes
1answer
24 views

access database normalization :how to deal book volumes like 1-2 or 3-4 [closed]

my question is about NF1 what is best practice to store book volumes in table . for example mostly it is like bookname = abc volume= 1 ect but some time it is abc volume 1-2.
4
votes
2answers
71 views

Does a computed column break 3NF (third normal form)?

From my understanding of 3NF, there should not be any transitive dependency. However, this is what I found from the AdventureWorks2012 database Production.WorkOrder table. Since StockedQty is ...
0
votes
1answer
29 views

Should you update child states based on the parent?

I have the following table structure Event --* Session --* Membership An event can be cancelled - reflected via a status column A Session can be cancelled - reflected via a status column A user can ...
2
votes
1answer
40 views

Database design - Members & companies represented by members

I'm struggling in database design problem and it seems I can't get it out properly. I need some help on this. Maybe it's a dumb case but I'd like to validate or correct my scheme and then go ahead. ...
1
vote
1answer
34 views

Estimate number of rows in query results

Given the relation: donate(donor, type, amount, candidate, party, donate-date) N(donate) 21,000 - total rows V(party) = 30 - number of different parties V(type) = 50 - number of different donation ...
3
votes
1answer
34 views

Determining minimal cover of a relation

I am having trouble understanding why a certain set of functional dependencies is not minimal. We have a relation R(A,B,C,D,E,F,G) with the following dependencies F: 1. A->CDE 2. B->FG 3. AB-&...
1
vote
0answers
17 views

verifying non-additive join decomposition

I am reading a DMBA book and have come across non-additive (lossless) join property. The algorithm for decomposition into multiple tables is borderline useless for practical work, so I am frequently ...
2
votes
1answer
114 views

Does using composite keys violate 2NF?

From all of the database related reference books that I have come across, there is a universal rule that state a database table should be at least 3NF. From what I understand, a table is considered ...
0
votes
1answer
35 views

Mandatory and non mandatory attributes for products

I am trying to come up with a simple way of implementing the following design, but I always end up with something overly complicated. I am trying to design a database where companies can store ...
0
votes
1answer
43 views

How to find all participants of a chat thread?

In my database I have the following tables: user, post, post_participant, and post_reply, largely as inspiration from this answer. One thing I'm having trouble with figuring out though is how to find ...
-2
votes
1answer
32 views

Best possible design for a database about users, companies and departments [closed]

I have an app with Postgres and two tables: users and companies. A user belongs to a company so a company_id column is present on the users table. Now a company can have multiple departments so the ...
0
votes
0answers
17 views

Represent a set-theoretic formula within table data?

Say I have a user table user_id | first_name | etc. and a group table group_id | group_description | etc. and a junction table called user_group, detailing group membership. user_id | group_id ...
1
vote
0answers
15 views

Trouble with inheritance: Separate table implementation for disjoint entities and common ID

I currently have four disjoint entity types (competition, talk, award, publication) that are to be converted to tables. In order to manage certain other data for each, i.e. documents related to each ...
1
vote
1answer
74 views

Database Design - hierarchical structure

We are trying to redesign a database that currently holds thousands of parameters for different datatypes and currently has a flat data structure. We generate XML files from the database, which our ...
0
votes
0answers
12 views

Segregated databases transactions consolidation into one database

I have regions and every region has 5 locations. currently we have an application which is pushing and pulling data on the logged in user branch on which user has permission. Every location is ...
2
votes
1answer
36 views

ERD - Multiple FKs with only one of them being filled for each record

I'm designing a database schema for a music platform. Some of the entities as follows: track album band artist A "track" can have an "artist". It can also have a "band" which makes the artist ...
1
vote
1answer
22 views

Design datastore persistence based on state of the Entity

I have an entity Content to persist in the database. The entity has an attribute LifeCycleState that denotes the present state of the content. For the sake of elaborating further, assume we have ...
0
votes
0answers
20 views

What would be the easiest way to create database of packaging of package in warehousing database? [duplicate]

I am creating a warehouse application for a client where goods are received in boxes. The problem is, they contain more boxes in it. Like if I have a product say Apples, then they receive 5 Boxes of ...
1
vote
1answer
30 views

Data Deplucation and relationship integrity [closed]

I apologize in advance as this question is likely to be more on the theoretical side, with (hopefully) a fair bit of discussion. This is the scenario: We have Salesforce and a multiple local ...
0
votes
0answers
23 views

DB design - logging transactions

I am trying to implement a booking system for charging stations that can either extend or refund a booking. I basically have the following tables: booking booking_logs payment refunds With how the ...