Questions tagged [database-design]
The development of the conceptual schema and/or the logical model and/or the physical settings of a database.
6,231
questions
0
votes
1
answer
73
views
table inheritance: TPH, TPT and single table vs taxonomies
according to this post and many others web resources, you can do table inheritance with Table Per Hierarchy Inheritance, Table Per Type Inheritance or Single table (single table for all sub classes).
...
- 131
1
vote
1
answer
431
views
Create user mapping without password how to configure authentication
I am trying to create a user mapping in PostgreSQL without a password, but I am encountering an error that says.
local_db=> select * from employee;
ERROR: could not connect to server "...
- 11
0
votes
0
answers
44
views
Designing a scaleable social photo gallery in Postgres
I’m trying to design a scaleable approach to modeling a social photo gallery and user profiles.
Every profile will have 1 gallery.
My current idea is the following:
Photo table (id, photourl, fKey ...
- 101
0
votes
0
answers
14
views
GIS database table setup with multiple columns or FK
I am trying to design a environmental GIS table setup in a geospatial database. The workflow process is to create
a Permit To Disturb (PTD Table)
and for each PTD there will be a point, line and ...
1
vote
1
answer
1k
views
How can I insert multiple rows using a value returned from the first row?
I have a table, chat_rooms that has the following schema:
CREATE TABLE chat_rooms (
id integer GENERATED BY DEFAULT AS IDENTITY,
user_id uuid REFERENCES users(user_id) ON DELETE CASCADE,
...
- 145
0
votes
1
answer
105
views
structure booking system with different types of entities
I have a booking service with different types of entities.
For example: booking_restaurants and booking_houses.
I was thinking about 2 different designs.
1 booking table (booking with 3 columns, id, ...
- 131
0
votes
0
answers
24
views
Whats the best database and way to analyse time-series data changes
I started a project where I want to analyse data changes over time.
As an example this could be my model:
model TemperatureRecord{
lowest: float
highest: float
location: string
time: date
}
and I ...
- 101
0
votes
0
answers
18
views
Separating a single database into one user/transaction database and one time series database
My project involves storing sensor data (time series data) in db. There are about 5,000 sensors, each is sending data at 15 min to 1 hour interval. The existing service is using a single db, storing ...
0
votes
0
answers
44
views
How to prove this proposition which gives a easy condition to check a scheme is in 2NF
Definition of BCNF:
Let R be a relation scheme with the attributes U and a set F of functional dependencies (hereafter FDs). Then R is in BCNF iff for any non-trivial FD X -> A in F+, X is a ...
0
votes
0
answers
474
views
Why am I getting "cx_Oracle.DatabaseError: ORA-12541: TNS:no listener" error when I try to connect to oracle database from vscode?
I have a script I wrote to connect to my local oracle database. It worked perfectly before now. I was able to connect and populate the database with data from vscode. After I formatted my machine and ...
- 1
0
votes
0
answers
52
views
Should i rely on PhPMyAdmin's table normalizing tool or do i carry out the changes myself?
Full disclosure:
The images themselves are in spanish, i want to bring attention to the design only so i'm not modifying the former.
This may or may not be part of an actual information system that ...
0
votes
2
answers
836
views
Msg 214, Level 16, State 201, Procedure master..xp_cmdshell, Line 1 Procedure expects parameter 'command_string' of type 'varchar'
i am using SQL Server Management Studio 2018
I have created a stored procedure where i want to generate CSV Files
CREATE PROCEDURE generate_csv_files
AS
BEGIN
SET NOCOUNT ON;
DECLARE @country ...
- 28
1
vote
1
answer
31
views
Design when one column value can reference different tables
I have a table with machines data:
machines
----------
id
owner_id
// other data
We also have official machine data that comes from Germany and France.
Both have the same information:
# Machine data (...
- 113
0
votes
0
answers
914
views
Index vs Partition vs Multiple Tables
I have a table with billions of records.
So far we are indexing this table and we are thinking on partition this table based on a Key or creating multiple tables (one per each Key) to improve the ...
- 101
-4
votes
1
answer
127
views
Ask SQL Server to run a query but return results when i ask for results
I don't know if SQL Server can do this or not. It is just my idea of how i want SQL server to behave.
I want to send a Query to SQL Server but do not want results returned back immediately. Instead i ...
- 1
1
vote
0
answers
1k
views
Choosing between Entity-Attribute-Value or JSONB representations
I must store multiple types of documents in my Postgres database. I know the schema of each particular document type, I know what fields will be present. So, in theory, I could create a separate table ...
0
votes
2
answers
51
views
How to design a database that needs to merge similar data from different sources?
I'd like to scrape details of tennis matches from various websites and then combine them into a single database. Matches partially overlap across the websites and they contain different amounts of ...
- 83
0
votes
1
answer
43
views
Changing data type of an existing column of SQL database
One of the field in an existing SQL table is Decimal (5,5) with no foreign key relationships. Developers wants to change it to Decimal (7,5).
What will be the downside of making such changes wich has ...
- 1,039
1
vote
0
answers
29
views
Structuring an N-Gram-counts-over-time database
I’m working on an application similar to the Google Ngram viewer, tracking the occurrence of terms in articles from different news publications over time.
In the past I’ve worked primarily on front-...
- 13
0
votes
0
answers
77
views
Which is faster between regex and string length function, in searching string of different length?
I have a table named table1 having 2 columns named as user_id and col1. The table contains 5 million rows. This column col1 contains 3 types of values: first ending with char 'x' and string length 13; ...
- 21
1
vote
1
answer
232
views
Same primary key for multiple tables vs separate primary keys
Lets assume we have an employee table as below
employee
id (PK) | name | date_of_birth | age | created_at
We have a scenario where we need to add some metadata for each employee and not every employee ...
0
votes
0
answers
115
views
Find the I/O cost of the following question?
Estimate the I/O cost of retrieving records from Parts table that contains price less than or equal 1000 rupees. Assume that 15% of tuples satisfy the selection criteria. There are 3300 pages in the ...
- 1
0
votes
0
answers
17
views
How to manage works and editions in book catalog?
I am designing a SQL database for book cataloging.
I haven't decided yet what engine I would use.
There could be multiple editions/translations of the same original work, but mainly there will be ...
- 101
1
vote
0
answers
123
views
Is my food-tracker's database designed properly? (ER diagram + SQL Relations)
My idea:
The database will store foods, days, and "inserted foods" aka diet_logs (days and diet_logs have both existence dependency)
for each diet_log of a specific day, I will create a ...
- 11
0
votes
2
answers
94
views
Separate tables for logging?
I would like to log new products, updates to products and deletions of products (and the same for other schemata [customers, locations etc]).
Would it be best to have a separate table (e.g. ProductLog,...
- 111
0
votes
0
answers
79
views
mySQL workbench synchronize models removes existing foreign key constraint links
I have an existing database where the design is frequently updated (new tables, addition of columns, removal of tables/columns, etc). I initially created the database writing ddl code and saving it as ...
- 1
0
votes
2
answers
157
views
Set up PL/SQL Developer
i have got PL/SQL developer installed. However, it asks me to enter a password, username, and database. I dont know what to enter in this case. Am I going to need "oracle SQL client" to set ...
- 3
0
votes
0
answers
34
views
Translating given requirements into tables
New to SQL as I've worked with it for 24 hours.
I've been given a list of requirements which I would like to turn into various tables.
I've taken a stab at it and written down what I think is the ...
0
votes
1
answer
152
views
Is good idea to split the JSONB field into a separate table in my case?
In my case I have a table named clients that follows the following information:
store_listing:
#######
id SERIAL PK
title VARCHAR(100) not null
features JSONB not null
comments TEXT
vat_num VARCHAR(...
0
votes
2
answers
234
views
How to convert getdate() stored as varchar to date
I have stored 'getdate()' as text in one of my column in a table. (It's a complicated scenario ,how I saved it)
Now when I select the field what I am getting is getdate() instead of the date . How can ...
- 89
0
votes
0
answers
25
views
How to retrieve 2 column from 2 tables, column 1 and 2 are count from two tables
How to retrieve 2 column from 2 tables, column 1 and 2 is (count) from two different tables
and where condition from another table (table 3 )
my query is :
SELECT ( SELECT COUNT(*)
FROM `...
- 1
1
vote
2
answers
202
views
Advice: Should i use multiple physical databases or one physical database and several logical ones
We are creating a web application that will be used by companies as their business application every day, each company will have many employees and a lot of data.
Is it wise for each company to have ...
- 11
0
votes
0
answers
57
views
Error with CREATE DEFINER = root @ PROCEDURE
im getting an error in this script for my project. Im using MariaDB with HeidiSQL v12.2. I dont know what its happen with this script but the error occur at line 85. Sorry for my English.
CREATE ...
0
votes
3
answers
337
views
Solving the nested tables problem in relational databases
I'm coming from a non-SQL database (MongoDB), and I'm building up a new project, but with MySQL (a relational Database).
I'm making a restaurant system, and in this system, there's a page where you ...
- 103
0
votes
0
answers
143
views
Simple dynamic questionnaire creator design in MongoDB?
I've been researching and trying to learn how to create a dynamic survey builder architecture in Mongo by reading through the documentation and searching online for a while, but I'm still having ...
- 1
0
votes
0
answers
28
views
Guidance for database involving pdf and word files
First, many thanks for any guidance on my query. I am new to making a database and as such have little idea how to achieve what I envisage.
I work in mathematics education and would like to compile a ...
0
votes
1
answer
130
views
can date datatype varchar use index as date datatype datetime index?
I have an existing table that have a column date but varchar datatype and also a date datetime datatype
example:
tbl_name: trx1
columns: trx_id, activity, name, date(datetime)
index: date_of_birth
...
- 25
0
votes
0
answers
22
views
Design where child models can overwrite inherited data from parent model relationships
I have a webshop that sells certain products(posters, candles etc). The products can be configured with variants(These are things like packaging and extra add-ons etc). A product can inherit these ...
- 35
0
votes
0
answers
98
views
How to get exact SQL output for below mention input
Input table data :
itemid
MfrCatalog
MfrNameNorm
RawDescription
UOM
Conv
noun
Modifier
420754
G322H
ETHICON, INC
SUT PLAIN 3/0 27IN
EA
1
SUTURE
ABSORBABLE
420754
G322H
ETHICON, INC
SUT PLAIN 3/0 ...
1
vote
0
answers
25
views
Best practice to store subitems
I am going to create my first Dynamodb database table. I have 3 options in my mind:
1. Create a long table where every subitem is a new row.
Example:
user John | item 1 | 2021
user John | item 2 | ...
- 111
1
vote
0
answers
32
views
Doubt on 2NF decomposition
I have read that 1NF, 2NF and 3NF decompositions are lossless and dependency-preserving.
Consider this example on a relation R(A,B,C,D) with functional dependencies set as FD ={ AB->CD, A->C, BC-...
- 11
0
votes
0
answers
25
views
Do these tables need to be separated to ensure no nullable fields in 3NF?
I have seen several forums stating that using nullable fields is a violation of 3NF, which I need to use for a school task. In this task, I have two different types of bank account that I need to ...
- 11
1
vote
0
answers
48
views
Does this design have a transitive dependency created by foreign keys, and must a new table be made to put it in 3NF?
I am designing a database for a school task, which we've been told must be in 3NF. However, I'm very confused by the concept of transitive dependencies in the case of 3NF too.
Here is an example of a ...
- 11
0
votes
2
answers
190
views
Exec An SQL query in a table in Database 'B' from database 'A'
i have this Table sequence_data in database A . The table has create script for sequences . No I want to create all these sequences in another database B. If it is the same database i can just use a ...
- 89
1
vote
1
answer
214
views
Product Object for Ecommerce in NoSQL
I'm working on an ecommerce app that will be using a NoSQL document database. I want to have a general purpose product that can be used for different types of products, ranging from coffee to ...
- 113
0
votes
0
answers
114
views
Relationship in ER diagram
I have this ER diagram assignment for a sport club. I have shown different entities. But I have an entity for staff and then I have entities like instructors, admin, managers, coach. I have be to find ...
1
vote
2
answers
99
views
Help - Database design
We are implementing the new requirement in our current web application. If we discuss the database design for the new requirement, it appears to be more complex.
Requirement -
We have two webforms; ...
- 165
0
votes
1
answer
184
views
Performance PostgreSQL 14 with long running queries causes PG POD MASTER 0 (StatefulSet) to reach MAX CORES
I'm running My Backend on a physical machine as follows:
Backend Code in TypeScript
ORM is Sequelize
Kubernetes: K3S version v1.23.14+k3s1 (c62b03fb) go version go1.17.13
My Backend is running 2 PG ...
- 101
2
votes
1
answer
54
views
Preserving foreign key properties when using Class Table Inheritance (or alternatives)
Imagine I have a table events, which outdoor_events and indoor_events inherit from. I have activities then outdoor_activities and indoor_activities. Assume it makes sense for outdoor and indoor to be ...
- 198
0
votes
1
answer
56
views
Is mixed content in a column a bad design?
I have a colleague who keeps putting mixed content in a column. For example this current case I'm struggling with is a column called "opts" which is an integer array. Depending on a type ...
- 123