Questions tagged [database-design]
The development of the conceptual schema and/or the logical model and/or the physical settings of a database.
6,020
questions
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 ...
- 1
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 ...
- 101
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 ...
- 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 ...
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 ...
- 1
-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 ...
- 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 ...
- 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 ...
- 111
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 ...
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 ...
- 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?
- 11
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 ...
- 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 ...
-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 ...
- 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 ...
- 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 ...
- 11
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.
...
- 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 ...
- 1
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 ...
- 101
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 ...
- 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,...
- 101
-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 ...
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.
...
- 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 ...
- 11
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", "...
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 ...
- 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/...
- 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 ...
- 125
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 ...
- 23
-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 ...
- 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 ...
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 ...
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 ...
-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 ...
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 ...
- 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 ...
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....
- 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 ...
- 11
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 ...
- 1
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 ...
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, ...
- 357
-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 ...
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 ...
- 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 ...
- 1
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, ...
- 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 ...
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 ...
- 1
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 ...
- 123
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 ...
- 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 ...
- 195