Questions tagged [database-design]
The development of the conceptual schema and/or the logical model and/or the physical settings of a database.
5,861
questions
-1
votes
0answers
9 views
Automatically Update Data based on the two dates
I am creating a rental system (school project) using MS access database and windows form VB.net, I created a table tblRent that include rentStartDate and rentEndDate columns. I have another table ...
0
votes
1answer
30 views
Is it ok to have multiple files table for a different entity?
I have two entities A and B.
A and B can have multiple files associated to them, each of those files can be a document or an image.
Does it make sense to create a_files and b_files tables with the the ...
0
votes
0answers
10 views
Conceptual ERD for barber
In need of help, as I am a newbie on database design. So, on the first image I attached, it was based on my design. I am a bit confused about three things
is it okay for "Barber", "...
1
vote
1answer
34 views
Should I partition a database file for performance improvement if a file sits on RAID?
I read a lot about performance improvement if instead of a single large .mdf file you partition it by multiple (.ndf) files.
But I also found from documentation that the same improvement can be ...
0
votes
1answer
22 views
How to update a column with multiple row values for each group?
In my Postgres table, there are about 2 million rows in total and for each ticker, there are 752 rows. I have an algorithm that shows different prediction price values for each date and ticker which ...
-1
votes
1answer
31 views
Efficiently storing polled objects in SQL
I have an application that "polls" objects in other resources and store their metadata in a postgreSQL DB.
In the end, I would like to query these days changes (object was not present ...
0
votes
1answer
16 views
Database solution for online dictionary
I'm seeking advice concerning a database solution for an online dictionary.
I'm a front-end developer with very little experience with databases.
I have a JSON file with around 1.5 million lines of ...
-1
votes
0answers
22 views
2 tables with the same columns but serve different logical purposes
So I'm building a database for a project and I came across this issue..
I have 2 tables that have exactly the same columns, but not for the same purpose. The first table is for registering the ...
1
vote
1answer
25 views
Can a single parent table have more than one child table?
I am learning databases and sql. I am trying to understand if one single parent table can have multiple child tables, and if yes, just how common is it in financial companies? If this is allwoed, is ...
-1
votes
2answers
26 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
51 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 (stock metrics like returns, sales etc.), and the second table has 5 columns with a one-to-many relationship and on cascade delete ...
-4
votes
0answers
20 views
-1
votes
0answers
16 views
I want to update Remaining_Payment by add advanced_Amount with Sum of Recived_Amount from and subtract from Total_Amount ) By Triggers
Here is My Database tables create in SQL SERVER MANAGEMENT STUDIO 18
-1
votes
0answers
21 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
25 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
26 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
48 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 ...
2
votes
2answers
65 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
1answer
45 views
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
42 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
32 views
What could be the better Database to make a book reader one or more books? [closed]
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
43 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
86 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
31 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
22 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
50 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
81 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 ...
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 ...
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
48 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
16 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
22 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
37 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
19 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 ...
