Questions tagged [database-design]
The development of the conceptual schema and/or the logical model and/or the physical settings of a database.
6,189
questions
0
votes
0
answers
11
views
MySql: Ecommerce product variant structure
I have a problem with the database organization for ecommerce product variations. Googling, I found a lot of articles and every article says that if the product has more variations, it should be in a ...
- 101
0
votes
0
answers
18
views
Explain the following ER diagram in English sentences [closed]
Explain the following ER diagram in English sentences
1
vote
2
answers
64
views
Database design for a limited historical data per user
I need to store data related to timer sessions. My MySQL table looks like this:
user_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
timer_start_time TIMESTAMP
timer_end_time TIMESTAMP
There ...
- 111
0
votes
0
answers
31
views
How to use things like Elasticsearch, Meilisearch, etc. alongside a main database (e.g. PostgreSQL)?
I could be missing something obvious because this seems like a fairly basic question, but I haven't been able to find any explicit guidance anywhere on how to integrate Meilisearch/Elasticsearch with ...
- 121
0
votes
1
answer
32
views
Best solution for storing metadata and data from documents?
First of all do I want to mention that I'm fairly new to databases and big data.
Background:
I'm working for a large company who wished to unify all their documentation.
At it's core of my project am ...
- 101
-2
votes
1
answer
41
views
I am working on Hostel Management System and i am confused about database
I am working on Hostel Management System building on c# .Net and their are master section which have Hostel , building, wing , room , room type masters forms etc. In hostel form i am getting input as ...
0
votes
0
answers
22
views
Multilple tables with same columns workaround
I'm creating an attachments logic in my system, but I don't know what solution would be a better practice
I made some diagrams:
Multiple table with same fields:
Only one table, but it references to ...
- 1
0
votes
0
answers
19
views
Is this small business database design on the right path?
I am designing a database for a small business that would like to use this system for a very long time, meaning they expect to maintain and grow the system but they want to start off well. This is ...
- 1
-1
votes
1
answer
31
views
What normalization rules flattening one-to-many violate?
I'm trying to better understand NF in a theoretical sense. The data I have feels wrong but I'm not sure if/how it violates normalization
Example table:
product
inc_charger
inc_cable
inc_headphones
A
...
- 101
-1
votes
1
answer
23
views
How do database snapshots work?
How do database engines take consistent snapshots when writes could be happening at any time?
(A) Does the database engine block writes one table at a time while taking snapshots?
(B) Does the ...
- 99
0
votes
1
answer
22
views
"extra columns" table, possible pitfalls?
Our database is currently bringing in data structures from third-party constructs that we don't entirely control. One major example is TIGER data from the Census bureau, loaded in via the postgis ...
- 121
0
votes
1
answer
46
views
How to rename columns in an SQL-server database
My employer has the habit of giving customer specific tables a name, starting with the name of the customer. This makes it difficult to call the sp_rename stored procedure for renaming column names, ...
- 523
0
votes
0
answers
24
views
Database design for a Destination Management Company Accommodation Inventory
I am putting together a small inventory management system for an accommodation contract. I'm working for a Destination Management company and we have contracts from different hotels that enables us to ...
- 9
-2
votes
0
answers
32
views
File processing in sql server/postgres
good morning guys,
I have a new project that works with processing files in edi format, all with layouts (txt) or by spreadsheets (csv/excel).
Today we received three files, one with registration data ...
- 1
-3
votes
1
answer
29
views
What database model to use?
I am about to start a SaaS application for companies (clients of the app) to mange their employees, customers. Application would have different roles in it such as admin, manager etc.
Do you use ...
- 1
0
votes
2
answers
50
views
Is a Composite UNIQUE Constraint On an Already UNIQUE Column Redundant?
I'd just like to clarify something pretty simple regarding single and composite UNIQUE constraints that I can't find a clear answer to.
Assume I have the following table:
CREATE TABLE person (
id ...
0
votes
1
answer
43
views
Storing segmented time series - performance issue caused by triangle relationship / maximum search
I am trying hard to create a database design from scratch, that allows to store multiple different time series, where each one may be segmented into individual epochs. Inserting new data points for a ...
- 3
-3
votes
0
answers
26
views
gDS for rapid development?
See TestingComplexSystems <.com> for additional substance. Given the role of the data store, gDS, in the development of "test platform" applications, I was wondering if gDS would also ...
0
votes
1
answer
25
views
Using view for only one table
Sorry If my question is too dumb. Recently I have learned about database view. As far as I understand view is mainly used to encapsulate complex query, Handle security related issues like permission ...
- 1
-1
votes
0
answers
35
views
Relational DB engine with support for column as a list of foreign keys
Is there some sort of DB engine that is relational (like MySQL's InnoDB/PostgreSQL)
and supports a type of column that is basically a list of foreign keys?
Is something like that in some standard and/...
- 99
0
votes
0
answers
30
views
Noobie needs help on designing database for membership management tool
I have searched through some of the database examples, but have yet to wrap my head around the logic of the relations between databases, so appreciate some support. Here is what I am trying to do: for ...
0
votes
0
answers
6
views
Uploading a file to Oracle database from Apex and then Writing to a Mounted folder
Have any one uploaded file in Oracle Apex from Client to another Client (The destination folder not on the database server).
0
votes
1
answer
66
views
Database design - normalisation query
I am a teacher and am querying a solution given by our examination board. The question is shown below and I'd like to ask for help in understanding why my solution would not be acceptable.
Here's the ...
- 103
0
votes
0
answers
11
views
How to represent that One entity is not a part of other entity in Conceptual ERD
In a conceptual ERD using crowsfoot notation, How can we represent that one entity is not considered as another entity? I think that if we don't connect these two entities in the ERD, then it is taken ...
- 1
-3
votes
0
answers
15
views
How to display amount of a certain model in DB and display the count on an HTML page
I currently have a MySQL database setup with an inventory of cars organized by things like make, model, year, body, etc. I was wondering how one would go about counting the amount of a specific model ...
- 1
0
votes
0
answers
30
views
Enumeration table, separate databases, readability and performance
I have this table called Locales. It's in the Globalization database:
Id Key Description
1 en English
3 ar العربية
4 ru Русский
I want to create a multi-lingual online shop. I have a ...
- 1,343
0
votes
0
answers
57
views
How can I improve the performance of a query that relies on data from lots of tables?
I am working on a Web application that renders a food menu in one view. The menu is modelled as one big tree, which contains nodes like services (breakfast, lunch, dinner), courses (starter, main, ...
- 103
0
votes
1
answer
28
views
Doctor CRM database design
I am designing a doctor CRM system. Now I need to have a list of patients in the doctor panel. what I do is I find all the bookings which are related to doctors and extract user data from there. Also, ...
0
votes
0
answers
10
views
Is this constraint can be modeled in the ER diagram?
I was asked to design an ER diagram for the following elections system :
election (edate,eno)
party (pname,symbol)
running (edate, pname, chid, totalvotes)
city (cid, cname, region)
votes (cid, pname, ...
- 101
0
votes
0
answers
13
views
changing the schema of incoming data for postgresql
I'm currently working on connecting a few large google sheets into postgreSQL. My current system is that the sheet gets extracted & loaded to postgreSQL, I'd then do the transformations ...
- 1
0
votes
0
answers
12
views
One to one Modeling?
What do you guys think of this model?
Catalog -< Catalog Items | - | Products
and
Catalog -< Catalog Items | - | Services
That is, a Catalog Item, can be one and only one type: Product or ...
0
votes
1
answer
31
views
Architecture for "Overlapping Dimensions"?
Take a SQL RDBMS data warehouse -- typical facts and dimensions type layout.
Say you want Orders x Country.
Maybe a date field, an orders field, a country field.
And then if you want a report/ ...
- 1,631
0
votes
1
answer
57
views
How to optimize a PostgreSQL table for heavy writes, yet still maintaining atomicity?
There are plenty of guides online on how to speed up writes on a PostgreSQL table, from tweaking the WAL configuration, turning off synchronous_commit, to creating unlogged tables. I'm currently ...
- 38
0
votes
0
answers
22
views
Help needed with database design
I am designing a real estate database to keep records of plots (lands). There are many aspects to it like sale deeds, buyers, sellers, etc. But specifically, the bit where I've got stuck and need your ...
- 1
0
votes
0
answers
27
views
Supertype/Subtype how to decide on representation scheme?
This textbook lists 3 options for dealing with a supertype/subtype relationship. However, it doesn't provide a lot of "reasons" to choose one option over the others. What sorts of things ...
- 121
0
votes
1
answer
29
views
Model availability for house rental database (AirBnB like)
I'm designing an house rental application database and I'd like to model the concept of availability for rooms. I'm still in the early phases of development so I'm thinking in terms of entity-...
- 1
1
vote
0
answers
40
views
Database revision needed for habit tracking database?
I'm trying to write a clean, maintainable, and easy to query database design for a habit tracking application.
Users can have habits, which have a goal of X minutes or repetitions every Y interval, ...
- 11
-1
votes
1
answer
39
views
Design and normalization issue : type of FK depends of an other field
I have a CapitalIncrease table, which contains a list of available payment methods. For each payment method, I need to put some data (SepaDocument OR Iban OR Crypto).
Here is a mermaidjs graphic of ...
- 1
1
vote
0
answers
23
views
How can I model this data in a er model
This is the description
This is a LMS system
There are Batches which have Grade, Medium, Syllabus and Year.
There are Tutors who can teach a Specific Subject.
The Subject has Grade, Medium, and ...
- 11
0
votes
1
answer
29
views
Partitioning using column from different table
I have a table ActionValues with the following columns:
AccountID
Action
ActionValue
This table is related with a second table called Actions that has the following columns:
AccountID
Action
...
0
votes
3
answers
74
views
Increased LIMIT results in performance degradation
This appears to be a variation of a common problem where a small change to the LIMIT clause in a query changes the query plan to one that has vastly inferior performance. In this case, I have two ...
- 1
1
vote
2
answers
45
views
How to ensure that a referenced record is unique across a subset of rows defined by a referenced record from another table?
Example situation
Let's imagine the following situation : A user can place orders. Orders are composed of items. Shipments are sent to the user. Shipments are comprised of items.
In order to optimize ...
- 113
2
votes
2
answers
269
views
Any pitfalls/benefits of creating PK with include(..) in PostgreSQL?
Application is aggressively caching data in memory and in order to support consistency (preventing persisting stale data) it is doing something like:
-- typical table structure:
create table t1 (
...
0
votes
0
answers
21
views
Help with design using ltree on PostgreSQL
Currently designing a database to register animal species (Sharks and relatives)
1 table will hold species (name, attributes, and relative information)
I will have the taxonomy hierarchy for each ...
- 101
0
votes
1
answer
56
views
Optimal strategy for storage of raw audio streams
This question is only about which DB system to use for the proposed system, and what the optimal schema/data representation for that DB system would be.
I am going to create a software system which ...
- 101
-2
votes
1
answer
54
views
I want to make a tree of parents only in sql server
My table in the database
How can I get my result as Where child = 'chl1'?
2
votes
1
answer
42
views
Constraint checks on multiple association tables vs. unique association table that centralizes these constraint checks
I have the following database schema (MySQL 5.7):
I have assistants that may be linked to either:
one customer (might evolve to multiple customers in the future, hence the association table)
one or ...
- 23
0
votes
1
answer
32
views
Best way to link data rows in the same table
In this example I have a table called "Zones"
create table stk_zones (
ZoneId int not null auto_increment,
Primary Key (ZoneId),
ZoneName varchar(255),
Height int,
Width ...
- 135
2
votes
3
answers
65
views
How to let you change the order of products?
On the database there is this table:
products:
id
name
type
1
product 1
1
2
product 2
2
3
product 3
2
4
product 4
1
5
product 5
1
6
product 6
1
7
product 7
2
the select to extract them is:
...
- 25
-1
votes
1
answer
52
views
Is there a difference between Range Type Partitioning & Temporal Data Tables (System-Versioned-Tables) in Maria DB 10.11.5?
I have a migration project (from Oracle) to Maria DB 10.11.5 (my choice).
The needs of the database server are not ordinary. The database is to write to disk the results of software bots which are ...
- 1