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

Dynamic table DB design opinions

i need some advice or opinions of "best practices" to clean up the mess i did (quick and dirty) a year ago (to show my idea) wich grew quite quick. We have around 10 tools (growing by 5 ...
0
votes
2answers
17 views

Is updating a table by delete+insert better than merging with a temp table?

I have two tables in Postgres. The first table has 120 columns, and each primary key has one row, the second table has 5 columns, and each primary key has 1000 rows (this table contains 10-year stock ...
-3
votes
0answers
16 views

Oracle XE 21c startup not working linux

SQL> startup ORA-01506: missing or illegal database name
-1
votes
0answers
14 views

Database structure for ticket states and types

We would like to store regular snapshots of our task tracking software in a database and I would like to ask about the best database structure for relationships between issues, types and states. Our ...
0
votes
1answer
21 views

Best practise making users in Maria db database

Iam creating a basic banking database to practise MariaDB, so I wanted to know what the general guidance on what kind of users you should be making and what type of privileges? Currently I have a read ...
1
vote
1answer
23 views

Effectively implementing associated values in a MySQL database

I'm a Swift iOS developer, and whenever I come across a data modeling problem where there is a property of an object that can have one of two different values, its type is usually going to be an ...
0
votes
1answer
47 views

Does combining multiple tables into one table with more than 100 columns improve performance and overall design?

I have 10 tables without any type of foreign key relations between them. All the tables have the same primary key. In this case, "stock_id" is the primary key. All table columns describe a ...
-3
votes
0answers
57 views

SQL server query takes a long time for short span dates but a short time for long span dates [closed]

Auto-create statistics and Auto-update statistics are also on for the database. The query is still taking a long time for the short span dates. Whereas for the long span dates, it is taking no time. ...
2
votes
2answers
58 views

Why does SELECTing sum() return null instead of 0 when there are no matching records?

This is but one of countless little details that make me frustrated all the time, forces me to memorize all kinds of special code and causes my queries to become uglier than they should have to be. ...
-1
votes
0answers
31 views
+50

How to design a complex multi approval expense db schema in MySQL

I'm very beginner in database design and had the following problem to solve. DB schema design for an expense approval system with these cases approval system, every company has 1 or many teams where ...
0
votes
1answer
36 views

Unknown column in field list MariaDB

I am using PHPMyAdmin and MariaDB and get this weird error as mentioned in the title. CALL log_total_outstanding(@message); MySQL said: Documentation #1054 - Unknown column 'time' in 'field list' I ...
-2
votes
0answers
23 views

Is there any mistakes in my table

I am beginner in programming I have just start new project and I want to make sure if there is a mistakes according to the flowchart that I attached. I want to make sure that my DB is designed ...
0
votes
1answer
29 views

What could be the better Database to make a book reader one or more books?

I am thinking about a personal book reading mobile app project. I have a total of 13 different books. Each book has 10 chapters and each chapter has its own content. It should have the feature of ...
-1
votes
1answer
39 views

How to query likes and user details for posts?

I'm pretty new to the world of databases and SQL. I'm trying to build a system for users to submit posts and like posts. I've been looking at a few similar questions on this site, reading about third ...
-1
votes
1answer
16 views

How should I construct a "custom synonyms" table?

I have a table called diary. I insert several messages into it almost every day. I sometimes, for example, call Microsoft "Microjunk", "Micro$oft", "M$" and many other ...
0
votes
1answer
41 views

Strong vs weak entity types on SQL

I have a 3NF normalized database design and was trying to understand the concept of strong vs weak entity types and my understanding is weak entity types are tables that don't have a primary key. ...
0
votes
1answer
27 views

What is good approach in storing price changes of a product / currency?

I am building a system where users can own a currency pretty much like crypto. The idea is that the value of that currency changes every 24 hours based on certain factors. Here's an example. There's a ...
1
vote
1answer
85 views

normalisation in DBMS

For the relation schema R(A, B, C, D, E) with the following FDs FD1: {A,B,C} → {D,E} FD2: {B,C,D} → {A,E} FD3: {C} → {D} I Decomposed R into a set of BCNF relations. I got candidate keys as {A,B,C},...
1
vote
2answers
38 views

Are these better represented as a one-to-many or many-to-many relationships?

I am trying to model the situation where someone can belong to multiple industries and have different years of experience in each industry. They can also know several languages and have different ...
0
votes
1answer
30 views

How to design tables to allow users to create their web page, with preview, save and publish features?

I am building a site, and among other things, I need to allow a user to create their custom landing page. Each user will upload their own header image, create a custom title, description, and other ...
0
votes
1answer
21 views

How can I arrange database structure for ranking card in a list in a Kanban Board?

thank you for helping me. I got a problem when I design my database structure for kanban board application. How can I design kanban board to rank card in a list for sorting,drag and drop,.. Currently ...
-2
votes
2answers
49 views

PostgreSQL efficient database structure

I am scraping products from a website. Each product has a price history table that contains a historical price with the associated date and store name. I would like to store this information in a ...
0
votes
0answers
17 views

Recommendations for a good Modelling tool for ipad

Apologies if this question is not allowed - but I couldn't even find the search box on the site to search for answers! I'd like recommendations for a good database modelling tool that works on an ipad ...
-1
votes
1answer
73 views

Understanding a double entry accounting DB Schema

This question is related to a previous question "Why the Accounting DB Schemas do not allow to maintain a clean Products Table?", asked on this network by someone else. Assuming that the ...
0
votes
1answer
22 views

How to design Postgres schema with multiple nullables?

I want to create a table for tasks in my database. There are a number of roles accounts can have in my app—admin, operator, user. All these roles can create tasks, and admin and operator can close the ...
-1
votes
0answers
44 views

Do I need separate tables for repeated values in single columns in my database?

I am trying to create a simple database to store thousands of various job postings. For the purpose of this question let's say I have 5 distinct job postings: +------------------+------------------+---...
0
votes
1answer
21 views

Possible to create a constraint that is unique per foreign key?

MySql, Table Repos Fk on Users.Id, A User can have many Repos however each Repo must have a unique Id, per User not per table, what is the best way to ensure that Repos Id is unique for a given User ...
0
votes
1answer
23 views

Model types of an entity [duplicate]

Let's take a simple users table. I have a few different type of users. Let's simplify it into two, employees and customers, for the sake of the example, but keep in mind I do have much more. So I have ...
2
votes
1answer
22 views

MariaDB index chosen differs on ARM and x64 based procesors

When I have been using mariaDB on apple M1 chip, I discovered some diference between index chosen in select statement versus x64 server linux system with this query: explain SELECT z.id_hlavni AS ...
-1
votes
0answers
33 views

Is this how you convert an original table to UNF?

Okay, so I want to convert my relational model with PK, Foreign Keys, and composite keys. But I'm not sure whether to add the foreign key and the composite keys to UNF or not. I am converting to UNF ...
0
votes
0answers
21 views

Maria DB not letting me creating more then 1 user at once

I am trying to make a read only and a full privledge admin user to my database with the following code: DROP USER IF EXISTS 'readonly'@'localhost'; CREATE USER 'readonly'@'localhost' IDENTIFIED BY '...
0
votes
1answer
39 views

How to fix MariaDB commands out of sync, missing expression near ON?

I have the following procedure DROP PROCEDURE IF EXISTS find_transactions_past_5_days; DELIMITER $$ CREATE PROCEDURE find_transactions_past_5_days (IN input_date DATE) BEGIN ...
1
vote
1answer
15 views

Is there a generator to create a table in MongoDB just like in Laravel?

I'm a newbie in mongoDB, and I just would like to ask a simple question. In creating tables in MongoDB, do you go ahead in MongoDB compass and create the fields in a collection, or is there a command ...
0
votes
1answer
52 views

How can I make a bidirectional relationship between tables?

I am designing the scheme of several tables (user and Courses / Seminars / talks) in which a user can have multiple courses. These courses are in a Courses list; this part is already related; but now ...
1
vote
0answers
81 views

Star Schema design considerations

We receive patient data that includes case level info (hospital id, patient id, length of stay, surgery date) as well as surveys/educational material that the patient completed as well as their ...
0
votes
1answer
17 views

How do I record an application session duration in SQLite?

Objective: Log the session duration of the user on the application. I have a session_log table: CREATE TABLE IF NOT EXISTS "session_log" ( "id" INTEGER PRIMARY KEY, ...
1
vote
0answers
19 views

How to log changed table fields in SQLite?

I have a table that looks like this CREATE TABLE IF NOT EXISTS "ebooks" ( "id" INTEGER PRIMARY KEY, "name" TEXT NOT NULL, "author"...
1
vote
1answer
71 views

Efficient short query for light tracking system

Considering this schema for a time series fights database named OpenSky: SET search_path = public; CREATE EXTENSION IF NOT EXISTS POSTGIS; DROP TABLE IF EXISTS "FlightsSummary" CASCADE; ...
0
votes
1answer
36 views

Survey DB Design. No user answers, only questions

The task is to SQL a bunch of surveys in order for the researchers to have examples for creating new questionnaires in the future (and insert those questionnaires into DB). So no information about the ...
0
votes
1answer
17 views

Mongo DB Query does not give desired result

I have two collections in mongo db. One called list with keys id and movieid in it. One called movies with movieid and Genre in it. The results I am trying to retrieve are: Which listid has the ...
0
votes
2answers
29 views

data structure for grouping children without parents

I have a list of name ID Name 1 John Doe 2 J. Doe 3 John Doe 4 John Doe With different methods, I check which names are the same person by checking their actions. For example, in ...
1
vote
0answers
65 views

Is this a good database design for course registration?

Specification: One course registration application form can only have one application type. One student can register (add or drop) to many courses. One course can be registered (add or drop) by many ...
0
votes
1answer
30 views

Pentaho does not read sql script (csv)

I was trying to use a different sql script for one job. For doing this i need to pass the script by file csv, so i set the script like a variable and then i make a transformation that "copy to ...
0
votes
1answer
69 views

How to spot all transitive dependencies in mysql database design?

I've read up on 3NF and transitive dependencies but had a couple of questions I'm unsure if I have achieved 3NF with my database design. Context: I want to create a basic bankings design where ...
0
votes
2answers
46 views

Confusion over using LEFT JOIN on multiple tables

I may be misunderstanding something basic here, but I'm struggling to find this issue being explained in my research. Let's say we have a users table and other tables that relate to the user, let's ...
0
votes
0answers
27 views

Best approach to convert a non trivial function (CASE WHEN) into a lookup (decision) table?

Assume A, B, C etc are attribute values in the current rowset Current function logic is similar to: Case A = 'Y' then Case B in (1,2,3,4,5) OR C in (3,4,5,6) then 'Sales' Case B in (99,98,102) OR D ...
2
votes
0answers
42 views

Which is the best approach for one table with some one to many relationships

I do not know which is the best approach in terms of database modeling. I have 3 kind of entities "Shipping Costs", "Billing costs", "Order costs" with different columns ...
0
votes
0answers
13 views

MySQL replication with read/write tables

I'm trying to implement replicas between master database and secondary databases. I've tried binary replication - it works as suggested. But I want use some service to manage this without manual ...
3
votes
1answer
65 views

Technical challenges for composite key

I'm sure this question has been asked before, but I'd rather put a twist on it to keep it not opinion based, and as informative as possible. (Also please forgive any mistakes I make in annotation, I'm ...

1
2 3 4 5
118