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
0answers
12 views

DB design: inventory items derived from other inventory items

I'm trying to design an inventory and project management system for a lab. It has a rather tricky element to it that I think separates it from the other "inventory" related questions I've ...
1
vote
1answer
105 views

what is best way to handle history tables in postgresql?

I am working on the following database structure/diagram for an app using postgresql 13 server Is there a better way to design these table relationships with the tables subscriptions_history and ...
1
vote
0answers
18 views

How to restrict allowed sub-entities in table referencing super-entity based on a type code

I'm working on my first db architecture for a platform containing several user types and that can exchange assets between them. There are also several transaction types, but each transaction type ...
2
votes
3answers
45 views

Preserve Autoincrement ID

I am working on an e-commerce webapp and here's what I am thinking about. To make it simple in this question, the user table contains two columns:id (int,PK,NN,AI),name(varchar(45),NN). I want the ...
0
votes
0answers
22 views

How to design and query graph relationships in PostgreSQL?

I am having difficulties trying to design a graph-like relationship in Postgres. For example: create table action ( user integer references user(id), relation varchar(255), target integer ...
0
votes
0answers
16 views

Multiple tokens per account

In the context of user account management, I'm using Firebase. So for each login, the front-end sends an Auth ID, a FCM token (for sending notifications to the user), and the usual user details. Each ...
0
votes
0answers
43 views

Database Scheme

I have a problem with a use case and I don't know how to design my database. Here are the detail : I have an entity "event" which can occur at different frequency but for each frequency ...
0
votes
0answers
26 views

How do i design a schema with proper DB engine to accumulate data depending on this need on clickhouse or in any other database?

We're a new Adtech company and I was planning to design a database where I'll pull all the data to a single table and then make new tables with a materialized views for others to generate multiple ...
0
votes
0answers
20 views

Foreign key on another foreign key && Pivot Table

I need to design a web app that function as poll system. It should have multiple polls with only 2 contestants which can be repeated in other polls too. For each contestant in the poll I need to count ...
0
votes
1answer
39 views

What database & design would you use for legos [closed]

I'm trying to conceptualize an interesting problem I am designing for. Using lego's (the plastic construction toys) as an analogy seems to work well. If this is a fairly recognizable problem, then I'd ...
8
votes
4answers
574 views

Performance Concerns with UniqueIdentifiers (GUIDs)

I am a software developer/architect at my company and occasionally our DBA whenever our primary DBA quits, so I think I kind of know what I'm doing, but this has me stumped as to the best course of ...
2
votes
0answers
26 views

How to; Schema for fitness data ("Analytics") [closed]

I'm currently working in renewing my fitness synchronisation product, and with it- the database logic/structure. I'm hoping to gain some expertise in the matter and how to think when this database ...
0
votes
2answers
35 views

Is there a way to examine all column definitions in MS-SQL?

I'm making a first program in MS-SQL. The idea is to make a proposal for a next tupple. One of the features is based on auto-increment (in case auto-increment is set, take the highest filled-in value, ...
0
votes
0answers
37 views

Multiple many to many relationship in one table on Postgres

I have 5 tables "element", "item", "file", "photo", "drawing". Each table can have a many to many relationship with all the other tables, including ...
0
votes
0answers
17 views

Would filtering by order ID from nvarchar(MAX) column benefit from fulltext search?

In my table I have a nvarchar(MAX) column which holds data about our orders in XML form. Another department often has to get information from the table by, most commonly, looking for a certain order ...
1
vote
1answer
43 views

ER-Model to relational mapping

"Fundamentals of Database Systems" by Ramez Elmasri covers this topic extensively, I've gone through it and seemingly the rules mentioned aim at converting the ER-Model to a relational ...
-1
votes
1answer
30 views

MySQL table design for history table

My requirement is to show the complete history of a client in the History page. We have following tables to maintain client data. Estimate (Id, Clinet_Id, Name, Total, Date). -- Estimate Data ...
1
vote
0answers
29 views

How to design a table schema to optimize insert and updates for a unique numeric column

Suppose there is a table with 3 columns id, name, score Name and score are both unique columns. The use case is that, The table is pre-populated with 10 rows. Scores are set from 1-9. A user can ...
0
votes
1answer
32 views

Can a foreign key be used for two primary keys?

I'm still a beginner in sql and I have to transform this relation between clients and addresses and vendors and adresses. This uses 2 primary keys(1 for the vendors and 1 for the clients). My question ...
0
votes
1answer
26 views

PostgreSQL - Share data on Network drive [closed]

I currently use a simple Microsoft Access database, stored on the company Network Drive, for my department's data needs. This works quite well as everybody in my team has access to the shared folder ...
1
vote
1answer
97 views

Column constraint based on values in another column

I have a PostgreSQL table phase_steps, with the following example rows: phase_step_id|step_type|step_status| -------------+---------+-----------+ 1| RESEARCH| | 2| ...
0
votes
1answer
57 views

How does MongoDB Treat Mixed Types?

I'm messing around with mongodb and noticed I can create mixed data types. db.getSiblingDB("bermuda").getCollection("test").insertOne({ "profile": { "t1&...
0
votes
0answers
29 views

Help find a usecase to understand sql vs nosql issue [duplicate]

I have very little background in SQL (one pet project with PostgreSQL that demonstrated only basic queries up to joins) and no experience at all with NoSQL. I understand that there are many DB ...
0
votes
0answers
36 views

How to Properly Configure a Time History Table?

I'm migrating a poorly planned table (in an Azure PostgreSQL db) that stores time history data, and I'm a bit overwhelmed. This history table has a composite primary key on 5 columns, 4 of which are a ...
0
votes
1answer
19 views

How to design table that has different columns for different category?

I need to design a database table where the columns will be changed on different category: System, User Input When the category is System, the columns are: id | orderNo | name | category | status When ...
0
votes
1answer
22 views

Data model for entities with a relationship to both guest users and authenticated Users

I am working on an application in which users can report security incidents.The idea is that everyone in an organisation has an account and can report an incident by logging in. Some of the users have ...
0
votes
1answer
70 views

MSSQL Join to put rows into columns column variable as column name

I have two tables TABLE 1 PRODUCTID TITLE 001 Product1 002 Product2 TABLE 2 ID PRODUCTID VARNAME VARVALUE 1 001 url https://... 2 001 length 30m 3 001 somethingelse somevalue I am trying to ...
0
votes
0answers
24 views

Question for my entity relationships

I'm not sure how I should think regarding my database design. I have made this database schema below. And I'm trying to think about the relationship between the entities. I have read through online ...
0
votes
1answer
113 views

db schema for storing multiple choices user settings

I am working on a project with the following data structure : A user need to be able to perform personal filtering on a given project, and this filtering needs to be saved (for each user/project pair)...
0
votes
0answers
61 views

Move address data into separate table

Currently address information is stored directly in the OrderHeader and Customer tables. I would like to create a separate Address table and replace the current address information with a reference ...
0
votes
0answers
27 views

How to convert array objects with object ids as one of key value pair to string?

I have the following JSON, How to convert an array of objects with object ids to string in MongoDB using aggregation query? { "_id": ObjectId("60e41eb7335412757cc6cb9a"), "...
1
vote
1answer
60 views

How to design a table with dynamic relationships in PostgreSQL?

I'm faced with a small challenge when designing a system events table. Take the following design: create table user ( id integer, username varchar(255), email varchar(255), password ...
0
votes
1answer
36 views

How can I include an entry for an intermediate row In a recursive query summing up children

I have a schema with activity(activity_id, parent_activity_id, name) and period(activity_id, duration, etc). An activity can have ZERO to many associated periods. Further, activities have a reference ...
0
votes
1answer
49 views

Is the database design alright? And I'm quite lost with the admin entity here

A quick description of my system would be: there are 2 users: users and admin. User registers on the website. Upon registration, the system will generate a QR code for the user. The user then goes to ...
0
votes
0answers
25 views

GDPR and database data relation

In my website, users can report comments. Those reports are verified by operators and, based on a conclusion, they will mark the report as "No action" or "Deleted". The "...
0
votes
0answers
20 views

Web Search or Redirect Tracking Data Model

I want to create a data model for Web Search or Redirect. We are hosting some web site asset. Some times visitor enters a keyword from search engine to enter the web site asset, some times visitor ...
0
votes
0answers
32 views

Track database changes cross-database

What is the best way (if it can be done) to track/log database changes (create and delete table) for several databases on the same node? If possible, I would prefer if all changes are kept in one ...
3
votes
2answers
407 views

How to add a generated column with an expression subtracting days?

I have this table in PostgreSQL 13: CREATE TABLE public."domain" ( id int8 NOT NULL GENERATED ALWAYS AS IDENTITY, domain_name varchar NOT NULL, -- more columns expire_date ...
0
votes
1answer
44 views

Should I use composite key or primary key from other table

We have a Education Project which has following entities : Domain (e.g. Programming, UI/UX, AI, ML), each Domain has 5 Levels (1, 2, 3, 4, 5) Building Blocks - which are like small topics e.g. java, ...
0
votes
0answers
23 views

Dynamic Tables from user input VS having Attributes and Values stored as Records

I am building an application in which user can define some entities and its characteristics (attributes / fields), as well as relations. Then, he can populate the entities with data. For example, user ...
0
votes
2answers
61 views

Seemingly simple query with unexpected execution time

I have the following schema : And want to get the last price (based on transaction_datetime) per test_acts.code using the following query : SELECT code, price FROM test_transaction_acts, ...
0
votes
1answer
28 views

Tables that Link To Many Other Tables (but only one at any time per record) [closed]

I am redesigning the application that drives our company phone system. There are various types of features within the phone application, such as menu systems, announcements, time-based routing of ...
0
votes
0answers
24 views

Most efficient way to update data that is "99% the same, 1% inserts/ updates."

I have a data source that doesn't do incremental changes. I need to pull the self-same 100 million row record set each day, whereby maybe 1% of the data is new, and 0.5% is updated. There sadly is no ...
1
vote
0answers
25 views

Stroring field name instead of referencing using objectId [duplicate]

This is my first question here For example: I have a post document which contains list of people who like it and my question is Should i store each person in the array using objectId or username(it's ...
0
votes
2answers
27 views

Designing complex cross table data integrity checks

I am trying to start working on an application, and while I have some code the time has come to work on my database model as well. In the next sections I will describe what my application should be ...
0
votes
0answers
35 views

Most efficient way to calculate user compatibility from per user

I'm looking for an efficient way to calculate user to user compatibility. A user has various preferences: age range (min & max integer) various choice based categories (multiple choice) distance ...
0
votes
1answer
82 views

Optimizer Not using Non Clustered Column Store Index for Update / Delete Statements

My Table tbl_FieldeData_ColStore Data Example: /NonClustred Column Store Index on Table/ CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI ON tbl_FieldeData_ColStore ( PDID,FieldID ) /Below Select was ...
0
votes
0answers
63 views

Is my ER Diagram correct according this business rules?

Tiny Hospital keeps information on patients and hospital rooms. The system assigns each patient a patient ID number. In addition, the patient’s last name, first name, date of birth, and date of ...
0
votes
1answer
36 views

Should I only have 1 table "User" Instead of 2 "Client" and "Worker"

I am worried about the 1 to 1 relationships. I read somewhere that it is bad practice? Also if I'm also worried about performance since I have to do a lot of joins. Please help. If anyone has a better ...
0
votes
1answer
16 views

Letting user select Unique Identifier of dynamic data?

I'm creating an app that lets companies view analytics about their employees. The manager of the company will upload all their employees' data and the app will run analytics on it. The problem is that ...