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
0 votes
0 answers
12 views

Junction (Bridge) Table Explanation

I have 5 tables: Customer Home Claim Owns Loss I don't understand how to select which of 5 is a junction table. Could there be 2 junction tables "Owns" and "Claim"? Or it has to ...
user avatar
0 votes
0 answers
7 views

Direction and Meaning of Names for Relations in ER Diagram (Chen)

According to Chen ER Diagram, I'm supposed to give a relation a name. It is commonly suggested to use a verb for the name (or just to concat the two entity names, but let's leave that aside now). I ...
user avatar
1 vote
0 answers
6 views

How to implement disjoint (nonoverlapping) subtype with total completeness in MongoDB?

I designed an entity relationship diagram (ERD) with the goal of creating two child entities (INSTITUTION and SPACECRAFT) of a parent entity (LOCATION), which is distinguished depending on the value ...
user avatar
  • 111
0 votes
1 answer
23 views

count the number of people who like me, number of people I like, and the intersection

i have two tables users and votes suppose I have a row in the votes table where uid = alice b_uid = bob liked = 1 this means alice likes bob I want to count the stats for a user Charlie, i.e. how ...
user avatar
0 votes
1 answer
20 views

Partitioning vs Archiving Data for a specific use case

I'm not going to ask the difference between partitioning and archiving as there are countless of information out there on the net discussing them, and I have read the details of partitioning as ...
user avatar
-3 votes
0 answers
21 views

implement a payment table for different types of services

In this app there are the following business rules: A personal trainer provides services These services can be Class or Plan type A plan can be subscribed for a certain number of time, having ...
user avatar
  • 1
1 vote
1 answer
30 views

Is database development stages the same as software development?

In software development, there are a series of stages that it will goes through - Dev, Test, UAT, Staging, Demo and Production. This is what I feel is correct, after reading/researching through the ...
user avatar
  • 143
1 vote
2 answers
37 views

How to Update and Manage Millions of Databases

We are looking to shard our application, so that each customer has their own database. The data contents of all databases are different, but the structure is the same for all customers. Imagine there ...
user avatar
0 votes
1 answer
36 views

Performance considerations when adding a new column to a big table in Sql Server

I have a table with 100 million rows and 70 columns. I'm planning on adding a new DateTime column to this table but I'm concerned that it might affect its performance. Would it be better to create a ...
user avatar
1 vote
0 answers
20 views

Data warehouse: solving many-to-many relationship with duplicates

I'm learning dimensional modeling and have a question. I understand those basic examples such as a FactSale has DimItem, DimLocation. But when it comes to complicated cases with many-to-many ...
user avatar
  • 11
1 vote
0 answers
39 views

Can a foreign key be a primary key?

I want to know the relationship takes, teaches, advisors tables underlined attributes are foreign key or primary key? or both?
user avatar
1 vote
3 answers
37 views

Is having a boolean column to store whether there are values for a multivalued attribute a bad micro-optimization?

This question is more theoretical. Let's say there is an entity in a one-to-many relationship. For example, a blog post can have zero or more tags. Those tags are stored in another table (that ...
user avatar
  • 23
1 vote
1 answer
30 views

2 foreign keys in a many-to-many relationship

Hello database experts! I'm designing a small database and ran into a situation where two foreign keys are in the same table referring to different rows in the parent table according to different ...
user avatar
-2 votes
1 answer
29 views

What prevents relational databases from being optimized for writes rather than reads?

I noticed that most of the popular relational databases systems such as PostgreSQL, MySQL, MariaDB, SQLite, etc. are oriented towards reads rather than writes. It seems that many of these database ...
user avatar
  • 107
1 vote
1 answer
31 views

Hotel booking database design

I am building an online travel agency and I have a concern with the database design. Suppose users will be of two types: Standard (users looking to book hotels) and Hotels. Obviously, there will be ...
user avatar
  • 13
1 vote
0 answers
11 views

Efficiency of pulling database regularly for doing statistics and ML statements for real-time analysis

I work with a data scientist to analyze data that are currently stored in postgresql database in real-time. We've got the idea to pull some data from the database regularly (from a timestamps) using a ...
user avatar
0 votes
0 answers
12 views

Using product price as index

I'm working on my first ecommerce website.In my product table design, i've created a non clustered index on name, slug,manufacturer because these fields will be involved in most of users researches. ...
user avatar
  • 103
0 votes
0 answers
50 views

Fixing my code to prevent sql injection [migrated]

I made a login with authentication in DotNet windows forms app and I'm trying to do my best to guard the database from SQL injection attacks, but it seems like there was a wrong logic in my code. Any ...
user avatar
0 votes
0 answers
16 views

Is this a good database design for storing recipes that contain "sub-recipes"?

To preface, I haven't designed or worked directly on a database in years, and my general SQL knowledge is still fairly limited. I'm designing a recipe database in Access to store interesting recipes I ...
user avatar
0 votes
0 answers
24 views

Help with a relational database design for users and purchasing business subscriptions

The part of the database I am designing is to complete the following requirement: A user can signup for a business's subscription service. Each business predefines a set of tiers(levels) for their ...
user avatar
  • 1
0 votes
0 answers
13 views

Keeping up-to-date values aggregated based on some time period

I'm considering a few solutions for a problem involving some sort of a time series data. Imagine: you have a system that keeps track of financial transactions in multiple currencies. To keep it simple,...
user avatar
-1 votes
0 answers
27 views

Many to many from another many to many relationship

I have a course planning project where a curriculum has many subjects (called a curriculum framework), and subjects can belong to many curriculums. This works perfectly for graduate courses. But in ...
user avatar
0 votes
0 answers
9 views

Key-value database options for spiking workloads?

I am creating the following ETL process: each day, update documents and write new values. This ETL process runs everyday, and takes 1 hour to complete. I need fast lookup, update and insert ops. ...
user avatar
  • 101
1 vote
0 answers
23 views

Many to Many triangle between three tables

I am having three entities: Student, Group, Class. All of them have many-to-many relationship: One student can attend many classes, and many classes may be assigned to one user. One student can be a ...
user avatar
0 votes
0 answers
17 views

How to organize optional cascading deletes of foreign key references (probably with triggers)

CREATE TABLE "t1" ( "id" UUID NOT NULL PRIMARY KEY, "other" text ); CREATE TABLE "t1_proto" ( "id" UUID references ("t1", "...
user avatar
1 vote
2 answers
83 views

How to implement/architect a SQL database schema which can handle types/tables with many optional properties?

I am working on a trimmed down database schema (in PostgreSQL) like the old FreeBase, just not quite as much stuff. There are about 100 tables so far, but that is before I'm considering adding dozens ...
user avatar
  • 155
1 vote
0 answers
42 views

Multiple databases vs partitioning in PostgreSQL

Consider following database schema for PostgreSQL (I'm using 13.x.x) Users table - just place to store users and do authentication Projects - projects which are created by specific user Driver/Event/...
user avatar
  • 111
0 votes
1 answer
24 views

How to automaticaly create entries in a parent table before filling child table? What is that called in db terminology?

What I want is for inserting the string name of a publication in a child table to associate with the PK of the publication in the publications table. For example, let's say I have a table of counties ...
user avatar
2 votes
3 answers
59 views

Database Design - Relationship between two or more junctions tables

I am a junior developer and I don't know how to implement this next relationship. Note: All entities described here are fictional but have equivalents in an actual production Database. Performance is ...
user avatar
-1 votes
0 answers
36 views

Does 3NF preserve implicit FDs?

According to the 3NF definition it preserve all FDs. Does that also mean that it preserve implicit functional dependencies ? Because if we decompose using 3NF synthesis, then we are sticking together ...
user avatar
  • 1
0 votes
1 answer
14 views

How to give Read_write users access to foreign tables imported through foreign data wrapper?

I had a question regarding the extension postgres_fdw. I am trying to use postgres_fdw to import foreign tables from DB A to DB B. These databases are on the same host. In Database B, I have a set of ...
user avatar
0 votes
1 answer
36 views

Distinguish between "take-away orders" and "orders for a table"

I have these tables: I have to distinguish an order: it can be an order to a specific table or a take-away order. If it's an order to a specific table, I just need to store table number and the ...
user avatar
0 votes
1 answer
21 views

Do Postgres Roles carry over from Databases if Databases are in the same server?

If I am creating a new Database on the same hostname/server, Will the roles/users carry over? Or will I have to create them again with new names and passwords? I see roles/users listed as server ...
user avatar
-4 votes
1 answer
26 views

create a oracle database user with no object in tablespace [closed]

Create user Sara with a password of sara. Make sure that any objects not created in the system tablespace and Sara can log in and create objects up to 10M in size in the MYUSERS. how to create a ...
user avatar
0 votes
1 answer
52 views

Opinion on my schema

I've decided to create a database as a personal practice. The project is based on a fictional English language school called "ESL" located in Japan. The school has 4 branches in 4 different ...
user avatar
  • 1
5 votes
3 answers
1k views

SQL Database vs Multiple Microsoft Access Databases

The company I work for produces a lot of test data in raw text files from lots of different devices. Each device belongs to a 'process' of which there are only a few dozen. Currently we have a program ...
user avatar
0 votes
1 answer
28 views

Designing large static Postgres database for quick index searching

Background info I'm sitting on 5000 files each consisting of 10 million lines and 15 columns. Each file i stores regression information for a given dependent variable y_i on an independent variable id....
user avatar
  • 101
1 vote
1 answer
29 views

Upgrading from excel to an actual database

Just started my first internship and I could use some guidance on approaching the project given to me. Simply put, all that needs to be done is to make a database and then transfer the data from a ...
user avatar
0 votes
1 answer
38 views

I need to improve my model

I create a smaller app that has to be used for money transactions where it focuses on favor and expense from bank accounts (later I will expand other tables for admin, workers costs, credit ...
user avatar
2 votes
3 answers
62 views

What will happen to dirty data, if PostgreSQL DB or server abnormally shutdown?

Suppose my PostgreSQL instance suddenly abnormally stopped and data is dirty and not saved in RAM or WAL. In that case what will happen ? Where from I can recover that data? and also if server also ...
user avatar
0 votes
0 answers
25 views

Should I avoid JSONB column for subschemas?

I'm working on a schema for an audit subsystem and I'm wondering about the tradeoffs of some potential design choices. About the schema, each event holds the same critical metadata event_id, ...
user avatar
-1 votes
1 answer
46 views

How secure is a local harddrive stored database?

I am currently creating a database that contains sensitive data such client names, addresses, etc. I have never worked with the setting up of servers but my research tells me to either use a cloud ...
user avatar
0 votes
0 answers
26 views

Recommended way to store IP addresses in traditional web app

I am curious if people could share different pros and cons for database structures storing IP addresses of users. The specific use cases are detecting if someone is using a malicious IP, as one of the ...
user avatar
  • 101
0 votes
1 answer
39 views

Multi master vs leader-follower

Sorry if I ask this basic question. I am reading different articles and trying to understand how it works. Lets suppose I have two master DB(MySQL) in two different availability zone and two different ...
user avatar
2 votes
1 answer
80 views

Is there a way to use time series databases effectively without absolute timestamps?

I have multiple sets of scientific data (European Data File files for EEG data) that I am reading in via MNE Python. Each EDF can be read in with a Numpy array that contains the time basis, say 0.00, ...
user avatar
  • 145
0 votes
0 answers
15 views

How to merge RBAC database with application database and avoid anomaly?

Inside database application we know physically, that only app_staffs can access app_warehouse. But when we try to merge users table and app_users table, there will be anomalies, because there are 2 ...
user avatar
0 votes
0 answers
24 views

data saving best convention for multi skills and language

I am confuse about best practice to save 2 fields data for example : 1 user can have multiple languages like English , Arabic , French etc and 1 user can have multiple skills like adobe , databases ...
user avatar
2 votes
1 answer
92 views

Constraint for 2 foreign keys to have same secondary column value

Suppose I have a table such as Users that has a id and a company_id I want to make a 2nd table that has pairs of user id's ex: (primary_key_id, user1_id, user2_id). The user id's must be in the same ...
user avatar
0 votes
1 answer
71 views

Database design for news feed

I have one table news_feed_card which contains the following columns: news_feed_card id serial card_type int published_at date Each news_feed_card can only have one news post. The problem is that I ...
user avatar
  • 11
2 votes
2 answers
314 views

Is there a performance penalty on using databases instead of schemas in a single PostgreSQL cluster?

I m partitioning my problem using a Domain-Driven Design approach. Several databases arise with low or null coupling. In the long term they will be separate databases on separate hosts, horizontally ...
user avatar

1
2 3 4 5
121