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
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 ...
1
vote
1answer
32 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 ...
0
votes
2answers
65 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
322 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
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
vote
1answer
87 views

structuring media uploads (users, images, videos) using sequelize.js

(using pseudocode to simplify) say I have a User (users table) User { id: PK } and I want to have them upload images and videos (media). I want to be able to differentiate between images or ...
5
votes
1answer
157 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 ...
-1
votes
2answers
31 views

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

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 [on hold]

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
31 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 ...
85
votes
3answers
29k views

Is a composite index also good for queries on the first field?

Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index? ...
-1
votes
1answer
39 views

Is this Database Model for university is correct [on hold]

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 ...
-2
votes
0answers
43 views

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

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, ...
7
votes
3answers
901 views

Foreign Key with a Constant

Let's say I have a table A, that has two columns: one is an ID for ThingA, and one is an ID for ThingB. The primary key is (ThingA, ThingB). Next, I have a second table, but this time it's restricted ...
3
votes
2answers
640 views

Table design for “Users & Groups” plus “Groups within Groups”

I need to represent users and groups, where groups can be nested within other groups. (Not my design choice – I am replicating another system and must mimic this behavior. I have no access to its ...
0
votes
1answer
26 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 ...
0
votes
1answer
348 views

Parking Spots Availability Design

I have a reservation table that has from_date, to_date, and parking_location_id. What is the best way to calculate location spots availability for each location and each hour? I tried to write a ...
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
0answers
22 views

DynamoDB architecture/design review [on hold]

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 ...
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, ...
12
votes
3answers
2k views

CouchDB and document versioning

I'm currently working on a wiki-esque application using CouchDB and am trying to implement a document versioning scheme. The way I see it there are two ways of doing this: Store each version as a ...
1
vote
0answers
23 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
14 views

DynamoDB: How to model data that's shared and queried by all users?

I am very intrigued by DynamoDB, and it works incredibly well when I model the data for my main use case for my application. That being said, there is one specific use case that I can't wrap my head ...
0
votes
1answer
360 views

Django Multi Table Inheritance and Preserving Child and Child History

Place can have many different one to one fields, that’s why it can have bookstore, restaurant, and hardware store. But can the same place be both a bookstore and a restaurant at the same time? Now if ...
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 ...
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/...
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
22 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 ...
2
votes
1answer
154 views

Lossless Join Decomposition Criteria

On Wikipedia, it says: The decomposition is a lossless-join decomposition of R if at least one of the following functional dependencies are in F+ (where F+ stands for the closure for every ...
0
votes
1answer
170 views

How to insert records in two different tables with PDO?

I have questions concerning signup process using PDO. I need to insert different records in two different tables that are connected. Employee id | f_name | l_name | position | paygrade | users_id |....
1
vote
2answers
1k views

Optimize large table design

First, I am a complete novice when it comes to databases, but have been given the task to speed up queries on a large set of data. (Hundreds of millions of records) The current implementation is a ...
0
votes
1answer
19 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 ...
41
votes
5answers
36k views

How to design a database for storing a sorted list?

I am looking to store a sorted list inside a database. I want to perform the following operations efficiently. Insert(x) - Insert record x into the table Delete(x) - Delete record x from the table ...
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 ...
-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.
0
votes
1answer
32 views

P2P message system design

Database noob here, just curious to see if my design could benefit from any improvements. My goal was to keep this as simple as possible: a user can create an account, two accounts can message each ...
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 ...
0
votes
1answer
446 views

Linking MS Access 2010 to Outlook Inbox: Including Attachments

I'm trying to set up a MS Access table linked to an Outlook folder. I've been able to use the external data wizard to set up a linked table, but the table seems to only include subject lines and ...
7
votes
4answers
2k views

How do I model data from arbitrary CSV's in a database?

I am setting up a web interface for data management. Users upload CSV or similarly structured files, and I want to store these in the database so they can do manipulations on them -- filtering, ...
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
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
3answers
44 views

How to PARTITION a table in mysql?

I read a lot of articles that PARTITIONing is not helpful in most cases. I believe it should speed up the queries in my case. I have a table with the structure of CREATE TABLE pages ( page_id int(11) ...
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 ...
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-&...
0
votes
2answers
594 views

Store parameters of various types

I am a (poor, young) scientist working to develop a database to allow a project to scale up. I am experimenting with MySQL and have had very encouraging experiences so far, but I am still novice and ...
1
vote
2answers
327 views

Foreign key as part of composite primary key or not?

I'm designing a relational database. This is what I'm trying to design: I will have production orders and products. A production order will have one product, but a product could be on zero or more ...
0
votes
1answer
42 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 ...
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 ...
3
votes
2answers
6k views

Grouping data into 5 minute intervals within a time range

I am trying to prepare data for a candle stick chart. I have some records on MySQL database as shown below: | Rate | Date | |----------|---------------------| | 0.001111 | 2017-11-...