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
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), );
user avatar
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 ...
user avatar
-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 ...
user avatar
  • 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 ...
user avatar
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, ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
-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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
-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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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,...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
-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 ...
user avatar
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 ...
user avatar
  • 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: ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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&...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
-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 ...
user avatar
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 ...
user avatar
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') ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
-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 ...
user avatar
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%). ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar

1
2 3 4 5
120