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 vote
0 answers
69 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 ...
Arad Alvand's user avatar
0 votes
1 answer
72 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 ...
nklsla's user avatar
  • 101
-2 votes
1 answer
49 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 ...
Pankaj Baviskar's user avatar
0 votes
0 answers
28 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 ...
Eliel's user avatar
  • 1
0 votes
0 answers
23 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 ...
Times's user avatar
  • 1
-1 votes
1 answer
34 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 ...
kiler129's user avatar
  • 101
-1 votes
1 answer
29 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 ...
AlfaZulu's user avatar
0 votes
1 answer
29 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 ...
Him's user avatar
  • 121
0 votes
1 answer
79 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, ...
Dominique's user avatar
  • 523
0 votes
0 answers
27 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 ...
Oversoul's user avatar
-3 votes
1 answer
32 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 ...
jon's user avatar
  • 1
0 votes
2 answers
56 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 ...
Twisted Tea's user avatar
0 votes
1 answer
46 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 ...
rellachs's user avatar
0 votes
1 answer
28 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 ...
pi3o1416's user avatar
0 votes
0 answers
33 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 ...
mor3dr3ad's user avatar
0 votes
0 answers
9 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).
muhammad waseem's user avatar
0 votes
1 answer
73 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 ...
Tiny's user avatar
  • 103
0 votes
0 answers
13 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 ...
Helen's user avatar
  • 1
0 votes
0 answers
31 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 ...
Saeed Neamati's user avatar
0 votes
0 answers
65 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, ...
Check12's user avatar
  • 53
0 votes
1 answer
56 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, ...
niloufar padkan's user avatar
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, ...
Yarin's user avatar
  • 101
0 votes
0 answers
14 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 ...
tichy's user avatar
  • 1
0 votes
0 answers
16 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 ...
Cliff Ribeiro's user avatar
0 votes
1 answer
35 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/ ...
user45867's user avatar
  • 1,651
1 vote
1 answer
116 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 ...
labega's user avatar
  • 58
0 votes
0 answers
24 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 ...
rgarayia's user avatar
0 votes
0 answers
78 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 ...
Him's user avatar
  • 121
0 votes
1 answer
79 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-...
magister's user avatar
1 vote
0 answers
57 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, ...
jacks's user avatar
  • 121
-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 ...
Valentin's user avatar
1 vote
0 answers
28 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 ...
Arkam Fahry's user avatar
0 votes
1 answer
38 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 ...
Alexander's user avatar
0 votes
3 answers
87 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 ...
bstovall's user avatar
1 vote
2 answers
60 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 ...
pjmv's user avatar
  • 113
2 votes
2 answers
276 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 ( ...
Andrey B. Panfilov's user avatar
0 votes
0 answers
22 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 ...
Tiago Conceição's user avatar
-2 votes
1 answer
85 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'?
Mahmoud fathy's user avatar
2 votes
1 answer
52 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 ...
Merinorus's user avatar
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 ...
Travis's user avatar
  • 135
2 votes
3 answers
69 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: ...
gotali4396's user avatar
-1 votes
1 answer
62 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 ...
Ed Nett's user avatar
0 votes
2 answers
97 views

PostgresDB - Updating a Table from a different Server

I have a Database on a server (external) that has multiple tables that I have a connection to. I have created a select query that does various joins and that gives me the desired final view of this ...
DataVick's user avatar
0 votes
2 answers
45 views

Altering table with 140+ columns is getting slower and slower

Problem Hi, I need a help with a database design. Right now, I have a database with 140 columns and 20M+ rows. The issue I face is following: I am adding new column approx. every week and every alter ...
FN_'s user avatar
  • 101
0 votes
1 answer
36 views

Moving to SQL. How to design the database structure for intensive updates on JSON fields?

I'm moving from a nosql database to PostgreSQL and my challenge is how to design it to be scalable using my data structure. Last time I used an SQL db was like years ago. Since than only nosql, so I ...
Alexandru R's user avatar
1 vote
2 answers
43 views

How to enforce entity existence across N-tables - postgres

Let's say we decided to split user table in two, one will have data related to authentication, another basic user description: user_table user_id | name 1 | Max 2 | Alex 3 | ...
ZiiMakc's user avatar
  • 135
0 votes
1 answer
102 views

What is the most straightforward way to create subtypes and supertypes at the same time?

I hope this isn't too basic a question, but I am hoping that the DB engineers here will help me out. Do relational DBs usually have a straightforward way of doing the following?: Create a supertable ...
Logos Masters's user avatar
0 votes
0 answers
18 views

Getting a list of tabe contents based on column value of different table

I have couple of tables. Table A: PName PComment isTierI isTierII isTierIII lastValueUsed BOX row 0 0 1 2 FREEZER row 0 1 0 1 BOTTLE row 0 0 1 3 BUILDING row 1 0 0 1 Another table that I have is ...
Tan's user avatar
  • 113
0 votes
2 answers
83 views

How to handle two separate databases? MongoDB and PostgreSQL

We currently have a Postgres database which acts as our core/primary database for our web platform. This database stores registered user accounts (people who have signed up for an account on our site) ...
AccDa's user avatar
  • 11
0 votes
2 answers
56 views

Varchar for columns that store numerics/ids?

There is a table that has a column defined as follows: text_id varchar(255) NOT NULL I know that using varchar is optimal for storing text because it allows for variable length columns but in this ...
Jim's user avatar
  • 123