Questions tagged [database-design]
The development of the conceptual schema and/or the logical model and/or the physical settings of a database.
5,637
questions
-3
votes
2answers
27 views
Using SQL, what are the steps taken to look up a user's email address, and the date that email was authenticated, when their username is known [closed]
what are the steps taken to look up a user's email address, and the date that email was authenticated, when their username is known using SQL
0
votes
1answer
27 views
How to break down MySQL big table into multiple to solve operational issue
This is my current table schema in MySQL
we are not facing any performance issue in our application but there are operational issue .
The size of this table is 7 TB (6TB data and 1 TB index ) and has ...
0
votes
0answers
13 views
Is this product table schema good or worth editing?
I haven’t created such structures before and decided to ask the experts. Is this a good structure?
I am concerned about the following questions:
I created the "product_size" table ...
0
votes
0answers
35 views
Can a database have an unnamed row?
I came across a question asking whether it is possible to have unnamed rows and columns in a database. I assumed unnamed implied primary key. I'm not sure whether my assumption is correct. When I ...
0
votes
0answers
11 views
interactive database with depent combobox
I have to do a tool to fill by user.
User must from three "dimension" choose one "area". "Area" is divided on "problems" and output is choice one "problem&...
1
vote
1answer
26 views
How is data of multi valued attribute say phone number of person relation is stored in table?
I have searched for the answers referencing my books as well as web but i could not find one. So here i am.
I am not sure what this question is trying to ask so help if anyone could.
-1
votes
2answers
25 views
Can the derived attribute (eg, age ) be present in the same table with the attribute it is derived from ( DOB)? If no then where should it be present?
I have an assignment that requires me to use particular attributes. The query that I have is. If I where to derive the attribute age from the DOB field, can the I use age as a field in my table? If ...
0
votes
0answers
22 views
DB relationships with historical tables review
Idea is simple. User -> Projects -> Sprints -> Tasks, but I need to keep historical versions for them. It will fit to my requirements or there are some changes to be applied? - or different ...
1
vote
1answer
18 views
text unique to the grain should be stored in fact table or dimension table?
I am modeling a star schema for user reviews using the yelp data set.
Each user review has a business dimension key, user dimension key and a bunch of data associated with the review. All of the ...
1
vote
1answer
24 views
Selecting data from a mapping table using a foreign key
I'm trying to make a simple Login sys where each user should be shown data from a list depending on their user ID Using MS SQL.
After searching for how to do it best i came across Mapping (pretty much ...
-2
votes
0answers
52 views
the Shore to Shore Shipping case study for SQL
1 Find all manufacturers who have built fewer than 2 ships that have shipped Concrete Forms and have never shipped Toyota Camrys.
2.List out the total of each item carried by a shipment piloted by ...
0
votes
2answers
25 views
Need help designing database, Create a table with additional columns/fields in the table, or create a new table for each different type?
So the tables that I have are Certificates, Approvers, CertificatesApprovers, Categories, Approvees, Status, AdditionalFields, AdditionalFieldsValues.
Many Certificates to Many Approvers relationship.
...
0
votes
1answer
24 views
Consolidate / sort / combine / merge data for dummies
I am looking for a non-scripting solution to combine data from 3 different tables (preferably using spreadsheet software like Microsoft Excel but a database would also work e.g. Microsoft Access). I ...
0
votes
0answers
34 views
Database ER diagram review. Is this a good database design ? [HELP]
This database For school management system.
Please review and help me with your ideas.
0
votes
1answer
36 views
conceptual schema
I need to check if I have the correct conceptual database model. My requirements
A club can play more competitions and there can be more teams in the competition
The owner, player or coach may not ...
0
votes
1answer
11 views
Child, Parent and Grand-Parent relationship where a child may not necessary have a parent or a grand parent
Here, I have some problem representing a hostel-room-inmate entities.
First an inmate is given a hostel seat,
Then he is allotted a particular hostel,
Then he is allotted a room.
My approach is that ...
0
votes
2answers
45 views
Car Dealarship Database Design
I have to design the following sentences for a company that sells different vehicles:
"The database must keep track of everyone involved(customer and staff) in the sales for vehicles. The sale ...
0
votes
0answers
5 views
Merge multiple InfluxDB 1.8 dbs into one + add tags with names of dbs
I have multiple InfluxDB 1.8 databases with same schema but different names of databases.
I need to merge them all into one database + add a tag with name of original database the data came from so I ...
1
vote
1answer
38 views
Personal Finance App Monthly Reports Database Design ( SQLite )
I am building a personal finance mobile app that supposed to generate monthly reports, now I have a problem, confused about how to design the database to be monthly, I need the wallet to get archived ...
-1
votes
0answers
33 views
Best Suitable Database Structure in Oracle for Multiple Questions and Answers
I am building a Form in which User Select Different types of Protocol
Select Protocol:
Protocol 1
Protocol 2
Protocol 3
If user select Protocol 1 Then Number of Disease (Disease 1, Disease 2) Will ...
0
votes
1answer
26 views
Nice Database GUI for Lots of Text [closed]
I am designing a workflow processing system. I have many (hundreds) of workflows that in many cases are variants of each other and have related characteristics.
In the beginning development stages I ...
0
votes
0answers
33 views
Write the SQL code that will list only once the Physician-Person appointments
Write the SQL code that will list only once the Physician-Person appointments. In other words it will not list twice an appointment between the same physician and person
Can someone help me solve this ...
0
votes
1answer
17 views
Optimal way to store leaderboards by month
I have a leaderboard table of users for current month. I need to save all leaderboards by months and display current/old leaderboard on site. Should i create a new table when every month stars? Is ...
0
votes
1answer
17 views
what will happen if MongoDB primary host is down and secondary is out of sync. will we lose transaction?
In our environment, we have one primary(A) and two secondaries(B & C nodes) running in Mongodb 4.0 version. secondary(C) server is newly added and sync is happening on that. Cluster is running ...
-5
votes
1answer
27 views
Write the SQL code to find the average charge by the family medicine physicians
We need to write the script to find the average charge of physicians who specialize in "family medicine.
But i'm confused on how to write this because when we look at the appointment table, where ...
0
votes
1answer
35 views
Table design comment_seen vs comment_unseen — migrate data from one to another
I have system that allows comments posted by users and tracks which users seen which comments.
The read/unread state is tracked within comments_seen table. Surely enough, it grow tremendously.
My goal ...
-1
votes
1answer
28 views
Linux PostgreSQL mount data from external ntfs disk
I have similar problem as here: Postgres tablespace on mounted usb
I want to have access to my DB on external hard drive from Raspbian and Windows 10. Not simultaneously. I just want cross-platform DB ...
0
votes
1answer
38 views
Do you think using ObjectID as field name is a bad practice? What can go wrong if I follow this design?
I design a MongoDB collection where I am planning to use mongo ObjectID as a field name. The ObjectID is a PostID and in the array, I track all the userID who liked or disliked the post.
0
votes
1answer
25 views
Cartesian product of same set in oracle not working [closed]
Here is an image from SQL PowerArchitect depicting what the database contains:
I am trying to run:
CREATE TABLE 4
AS SELECT Sells.StoreID as ID1, Temp1.StoreID as ID2, UPC, Price
FROM Sells, Sells AS ...
1
vote
2answers
33 views
Wich solutions is better for translate some data?
I've a little mobile app who need some translation. Currently, I just want to translate my app in one language, I don't think I'll need more. So I'm facing a problem, should I translate stuff in ...
2
votes
2answers
111 views
Inventory System Data Design Issue
So I have been trying to design a inventory management system for a Car Showroom. The problem with this design is that I can get a purchase but on selling the product I can't link it to my purchase. ...
0
votes
1answer
48 views
What could be the best DBMS to store airbnb data for price prediction [closed]
I have a school project in which I need to predict the airbnb listings prices in 5 different cities.
So I have about 400,000 records each with 19 columns.
All the examples on the internet that are ...
1
vote
0answers
26 views
Multiple tables vs rows for timescaledb / postgresql
I am designing a postgresql/timescale database where I will be recording trades on multiple stocks. For each stock I will be saving the following columns: Trade time, Attribute 1, Attribute 2, ...
1
vote
1answer
22 views
Proper form in relational meal database
I am trying to design a database solution for the following:
A table containing all nutritional information regarding a given ingredient; ingredient_ID is the PK here but ingredient name has a 4 ...
0
votes
1answer
26 views
MySQL table design
I'd like to have a 'records' table (lets say this table is populated with some values I've created before-hand)
I also want a user to be able to create his own records (And only he will be able to ...
0
votes
2answers
45 views
Is it a good approach to design tables according to the queries I'll perform on it?
Watching this video, and pretty new to dbms.
The speaker explains that in a row-oriented DB, rows are read in blocks.
So, my understanding is that if I have rows with fewer fields, more rows can fit ...
-1
votes
2answers
51 views
Are junction / join tables always necessary
I have tried looking across other answers for this, so far I have not quite found what I am after. While there are multiple questions regarding junction tables, none seem to address whether a junction ...
-1
votes
1answer
52 views
how to design a highly perfoemance database query to do the following?
Given 3 tables, students(id, name) , courses(id, name) , grades(id, course_id, student_id, grade), find the top 100 students based on their average grades sorted descendingly by the average grade and ...
1
vote
1answer
22 views
ER Schema - bank system
Hello i'm trying to model a simple banking scheme
where I have 3 transactions: withdraw, transfer, deposit.
of these 3 operations, the transfer is the only one that needs more attributes, I need to ...
0
votes
0answers
10 views
Group records by similarity in Postgresql
I have a large table with Tags records. It has a varchar field name. I need to review all those tags and remove near duplicated rows: singular/plural forms, misspellings, a/the, separated or joined ...
1
vote
1answer
35 views
Is it a normal practice when there're 2 columns and if one is true the other must false, and vise versa?
I have something similar to this, but not precisely it:
I sell only coffee and tea, and for the next 50 years I'll be selling only these. In a table "drinks" I have 2 columns: is_coffee? and ...
-2
votes
1answer
28 views
Are unstructured databases faster using arrays or many binary variables? [closed]
Is it faster (or otherwise better) to structure unstructured data like this:
{ label:"many_variables_example",
"a":0,
"b":1,
"c":1,
"d":1,
&...
0
votes
1answer
30 views
The most generic e-shop DB schema
Let's say I create a schema for an e-shop what potentially sells everything: laptops, phones, tires, snacks etc.
Considering this, I cannot create a new table/entity for each good category the shop ...
1
vote
1answer
66 views
How do I return all column values for the recently inserted row/rows?
I have these two tables in a PostgreSQL 12 database:
CREATE TABLE public.test (
entity_id uuid PRIMARY KEY DEFAULT uuid_generate_v4()
);
CREATE TABLE public.test_registration (
entity_id uuid ...
-4
votes
1answer
51 views
Given a large table (say “users”) that receives many writes, which of the following SQL statements will not lock the table against writes for others [closed]
Statements:
Vacuum analyze;
Vacuum full;
Create unique index concurrently on users ....
Create index on users .....
Insert into users....
Alter table users add constraint 'new_check_name' CHECK (...
0
votes
0answers
19 views
running DDL and DML statements on multiple customer Mysql databases
We have a dozen customers who each have their own database (they are all on the same RDS instance). The database schema is exactly the same for all customers.
When a new code deployment feature ...
0
votes
0answers
33 views
import .dmp in Toad : Invalid format of Import utility name Verify that ORACLE_HOME is properly set
When I tried to import a DMP file using TOAD (9.0.1) Import Utility Wizard I get an error
Invalid format of Import utility name Verify that ORACLE_HOME is
properly set
Any suggestions please?
I ...
-1
votes
1answer
34 views
What is the best way to create descriptive labels for tables and columns that have unique alphanumeric names?
I have a database of 1,050 tables with unique alphanumeric names like B02001, B07009, B20017F, each with columns named B02001_001, B02001_002, B02001_003, and so on. I'm trying to create a way to ...
0
votes
0answers
31 views
Versioning in parent child relationship - stale foreign keys problem
I have modeled a parent-child relationship in the following way:
Following are my entities:
Task: Holds details of the task.
Event: Holds an event of status change of a task. For example, task_1 ...
1
vote
0answers
25 views
How to select 1 userID, and not every single one [migrated]
Currently, this .php file assigns to each chore, every single User that exists in the database, I think this is because it repeats, how do I make it only pick 1 random UserID instead?
<?php
...
