Questions tagged [database-design]
The development of the conceptual schema and/or the logical model and/or the physical settings of a database.
5,991
questions
0
votes
1
answer
19
views
MySQL code error
can someone please tell what is wrong with below SQL code?
CREATE TABLE student (
student_id INT PRIMARY KEY,
first_name VARCHAR(20),
major VARCHAR(20),
);
0
votes
1
answer
17
views
Find Consecutive Date in SQL using 2 date columns
I am trying to group by all consecutive patient admission to the table. If the patient re-admitted to the hospital next day( or within 24 hours), then I need to keep only one admission stay per ...
- 1
-1
votes
0
answers
20
views
PostgreSQL vs MongoDB which is better in storing Json data and retrieval and perform query operations [closed]
I am currently using Document store (Mongo database) for storing and processing Json data. I was aware that Postgres have similar option to store and process Json and all have all capabilities as ...
- 1
0
votes
1
answer
20
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
0
answers
24
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, ...
- 193
-1
votes
0
answers
28
views
How to create a database that can handle recording data on many types of gardening processes
I am wondering what is the best way to create a versatile extendable system for recording various types of gardening processes in a database.
There are many different processes that I perform that I'd ...
- 11
-1
votes
0
answers
22
views
Help with unavoidable circular dependency
Suppose I have these tables: Person, Report, Task
One person has one or many tasks.
One task belongs to a person.
One person has many reports.
One report belongs to a person.
Let me explain a bit ...
- 99
-1
votes
0
answers
25
views
Designing database schema for orchard
I'm creating a database for my orchard to track gardening processes in the database easily e.g. applying fertilizer, pruning trees and taking measurements.
In terms of database schema, would it be ...
- 11
0
votes
0
answers
11
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
20
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
79
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
-4
votes
1
answer
32
views
List each depot name and the total salary for bus drivers working at the depot. USING SQL [closed]
Sorry, but im not sure how to properly format on here but i have 2 tables
where i have to answer the question in the title. I have no idea how too but it's homework. I keep seeing things on joins but ...
0
votes
1
answer
39
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
-3
votes
0
answers
29
views
How to save a SQL diagram as Pdf?
I tried to save the diagram as an image using the "copy to Clipboared" function and then paste it in Paint and Word. But only the tables are copied and not the content ( the attributes) of ...
- 1
2
votes
2
answers
290
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
0
votes
1
answer
36
views
How to constrain row values in one table to match row values in another table?
Given:
CREATE TABLE operation
(
id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY;
);
CREATE TABLE listing
(
id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
operation_id BIGINT REFERENCES ...
- 929
0
votes
1
answer
61
views
When Insert Press Name Tech Id Take Wrong Number Where Press Name Exist?
I work on SQL server 2017 I have table #Pressfeature as below
create table #Pressfeature
(
PartId int,
PressName varchar(300),
TechId int
)
insert into #Pressfeature(PartId,PressName,...
0
votes
0
answers
23
views
Design proper schema for flexible entity field
I have some entities modeled like on the picture.
Every entity have field classifications which contains list of Classification.
Classification itself can have any name and value, all represented as ...
- 101
0
votes
0
answers
17
views
Querying across Many to Many Relationship to find all unique elements in jsonb array
Imagine two tables one for Collections that has many posts and one for Posts that has many collections it can in. The Post table has a Tags column that stores multiple tags in a array stored as a ...
- 1
1
vote
0
answers
29
views
Best Method for Modeling/Implementing Dependent Primary Child?
I’ve implemented Primary Child functionality using a third table as described by @ypercube here (and other posts). I chose this method because it’s database-agnostic, simple (no triggers or deferred ...
- 11
0
votes
0
answers
8
views
Records in table not showing in MS Access
I created database system using VB6 and MS Access database. It has been installed many computers and working fine. But I have a problem. Though can retrieve the data from VB system, when open the ...
0
votes
0
answers
20
views
Data modeling for employees with manager's record updated weekly
I would like a best practice or some direction on how to handle employee data, as I just took ownership of the database management
The table employee looks like this
id
employee
manager
week
score
1
...
- 101
-4
votes
2
answers
45
views
Does it make sense to use a non-relational database for reporting/BI/visualisation tools?
Relational databases are well used in reporting/visualisation tools such as Power BI, Tableau...
Non-relational databases are getting popular and many reporting tools allow its connection. However I ...
1
vote
1
answer
37
views
How to deal with fact table data that needs to be version controlled?
I have the following simplified sport_match 'fact' table:
match_id
tournament_id
player_id_p1
player_id_p2
p1_final_score
p2_final_score
1
1
1
2
1
0
2
1
1
2
3
1
3
2
3
2
2
3
4
2
3
2
4
0
The table ...
- 131
2
votes
1
answer
45
views
Is it a bad idea to have a "kind" column on a join table?
Is it a bad idea to have a "kind" column on a join table or should I just create separate tables for each kind?
For example:
Table A: Person
Table B: Song
JoinTable A: Person_Song (Kind: ...
- 123
0
votes
1
answer
19
views
Associating transactions with payouts in marketplace
I'm developing a marketplace-style application that allows users to upload purchasable digital items -> the public purchases these items -> and for my application to pay the users (owners of ...
0
votes
0
answers
12
views
What is the best way to track user ip and id for the page visits
I am 15 pages in my site. I want to track the guest user (through IP) & loggedin user (through userid) for all the pages.
What I have schema is:
Create a table;
create one entry;
and make 15 ...
- 101
0
votes
0
answers
25
views
insert missing records into table has sequence id
I am using SQL server 2019, enterprise-production-
Before the deployment, I took backup for the DB
Let's call it BK1
After the deployment, we had to rollback
Before rollback, I took another backup ...
- 31
0
votes
3
answers
39
views
How to optimize table for arbitrary user-made queries?
Let's say you have have a large-ish database table, and you'd like to allow users to query it with an API, using several different filter and sort options, and of course supporting pagination.
What ...
- 1
0
votes
1
answer
18
views
Do I have the right Slowly Changing Dimensions type for my version controlled tennis match database?
I'm trying to version control my database using the principles of Slowly Changing Dimensions. I've opted to use Type 2 with a generation start and end column instead of datetimes.
In a simplified ...
- 131
0
votes
1
answer
21
views
How to daily download and merge a csv file and update on Google sheets?
Disclaimer: I’m not sure if this question goes on here. I’ve trying to solve this issue for quite a while and I don’t think there is another way other than using a python script.
I need to solve this ...
- 101
0
votes
0
answers
47
views
Large table taking forever to return query results
I'm working on a [Rails+PostgreSQL] project where we have a table called attempts that has 13,578,350 rows.
Getting that count (Attempt.count, which converts to SELECT COUNT(*) FROM "attempts&...
- 163
0
votes
0
answers
32
views
Modeling a one-to-many relationship with reverse enforcement
I have 3 tables created as
CREATE TABLE agenda (
id SERIAL PRIMARY KEY NOT NULL,
title TEXT,
documents INTEGER[] REFERENCES document (id)
);
CREATE TABLE orders
(
id ...
- 1
0
votes
1
answer
41
views
How to create a reference in a many-to-many relationship with transact SQL?
I am creating my first database using only code (I don't like to use graphical interfaces, I feel that I am not learning to write T-SQL code, it is my personal opinion), I do not know how to create ...
- 11
0
votes
0
answers
16
views
How to create a junction table for three many to many relationship?
I have three tabels named student, question and exam.
A question can be asked in multiple exam.
A student can appear in multiple exam.
A exam can be attended by multiple students.
And I need to ...
0
votes
0
answers
30
views
MS Access and the Dataverse -- lost primary keys and read-only queries
I'm trying to create a database application with tables stored on a Dataverse/Teams backend and an MS Access front-end. I have a simple one-to-many database design, and a form with a query as its ...
- 3
0
votes
1
answer
48
views
Indexing first and last item of a series only
Does PostgreSQL support any kind of sparse index that would be useful for indexing only the first and last value in the table, based on a certain key?
Consider the following data:
CREATE TABLE samples ...
- 235
-1
votes
0
answers
16
views
Is my relational model with the 3NF normalisation correct?
I am currently trying to create a database and I am first starting with the creation of the diagram (both the ER and the relational one, with the normalisation 3NF).
Here is my ER diagram:
and here ...
0
votes
0
answers
18
views
This vs That: 1 table with partitions and sub-partitions or 1 tablespace 4 tables with partitions and no sub-partitions
This vs That: 1 table with partitions and sub-partitions or 1 tablespace 4 tables with partitions and no sub-partitions
Now for this instance in my current database structure I have 1 TABLE called ...
user253088
3
votes
1
answer
63
views
Storing column for data that doesn't exist for majority of rows
I'm using mariadb.
I have a single table that keeps track of video_count and image_count for a few different sites. I.e.
type | enum('SITE1','SITE2','SITE3')
...
- 211
1
vote
1
answer
31
views
Database Design: Year of Birth /Year of Death best practice - Postgres
I have a table of people (authors), and columns for Year_of_Birth and Year_of_Death. These are integers, as I wish to be able to compute things like current_age and age_at_death and ...
- 121
0
votes
0
answers
17
views
Postgres execute requests isolatedly
Users submit schema and query. I need to execute them and return the result.
But if multiple requests have the same table in the schema mentioned, then I am getting conflicts (which is as expected). I ...
- 101
0
votes
1
answer
38
views
Prevent creation of duplicate rows in database dynamically
I am new to databases so please forgive my mistakes. Here's the scenario.
I use the web for my data source.
Sometimes, like, for a keyword, what my crawler do is crawl same keyword repeatedly (not the ...
-5
votes
0
answers
17
views
How to create database query in MySQL to build a network of at least 30 different business partners to show commission earned by each for every month?
The following queries required:
1] Provide the commission tabulations due each month to associates
2] Along with the commissions for each associate provide the actual number of active memberships that ...
- 1
0
votes
1
answer
54
views
Logging Which Columns Have Been Read Or Written To
I have a Microsoft SQL Server database with dozens of tables and perhaps thousands of columns. Most of the code that ran queries against the database (read and write) is now out of service (like 80%). ...
- 109
-3
votes
1
answer
78
views
How to convert data separated pipe to rows?
I work on sql server 2017 I have table name #countriesData data as below
my issue here How to convert countries column data separated pipe to rows .
meaning every rev and company and year will have ...
- 107
0
votes
1
answer
18
views
Creating new table for new fields
I have never made a database with the ability for an administrator (meaning, a admin client using the front end interface) to create custom fields for an entry.
Thinking this through, I think it might ...
- 103
0
votes
0
answers
33
views
Database for dynamic number of hierarchy levels
My client wants to support dozens of cities where each city has its own graph-like data structure to store location hierarchy, for example:
City 1: Region -> District -> Street -> Shop
City ...
- 11
1
vote
1
answer
49
views
How to deal with invoices that reference products that don't exist any more?
I am developing an ERP using PHP and MYSQL. I have a product page and an invoice page. I am facing an issue, if I delete a product from products page, and I already created an invoice with this ...
- 11
0
votes
0
answers
31
views
Design database in which a table structure depends on value of a column
I am designing a database which will hold results of some atomistic calculations and I face a design problem. Namely, in a table named physical_properties I have an ENUM field type_of_structure which ...
- 101