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
votes
1answer
38 views

what is the optimal way i should design my table?

i have a table with 4 fields: standard auto_increment id user_id(two entries can be present with same user_id) product_id(two entries can be present with same product id) (both can be present in two ...
1
vote
0answers
55 views

What is the minimum hardware requirements for SQL Server Big Data Cluster?

I am in process of making POC and would like to test SQL Server Big Data Cluster solution. My first idea is to host it on my local computer and test it but I am not sure If it possible. In found ...
0
votes
0answers
42 views

Is this a solid database design that passes 3rd normal form?

I am a tennis coach and created a small database to track student testing. I have several programs( beginner, intermediate, advanced). And Throughout the year/season, they are divided into four 8 ...
0
votes
2answers
49 views

Choice between two antipatterns: Overdesign versus Over-optimization

Sometimes I need to keep state, like an IsVerified flag. There are two options: boolean (or bit) Advantage: smallest and fastest ...but in theory "over-optimization" and thus a bad argument. ...
0
votes
1answer
42 views

How to design banking financial transactions database schema?

I'm writing a simple banking database schema. It's for learning purposes, not for real a bank. Now I'm stuck at designing financial transactions. Transactions can be of different types: Money ...
0
votes
0answers
22 views

Should I create separate tables for things like favorites, bookmarks?

I am using Python with praw and pyside6 to build a desktop application that uses the reddit api to get posts. My main goal is to be able to favorite and bookmark the posts I want. At first, I used a ...
0
votes
0answers
44 views

Pulling in 2 million records monthly and deciding to update or duplicate

I will be pulling nearly 2 million records monthly in the form of a CSV from a government website. From there, it will be put into a postgres DB. The trade-off needs to be discussed because in ...
0
votes
1answer
60 views

In a food composition database, how would you model a food's relationship with its nutrients?

I am building a Web application that shows the nutrient content of foods. My data is currently stored in a foods table, where each nutrient is a column with a numeric value, e.g.: name sodium ...
0
votes
2answers
49 views

db design for storing inventory per customer

We have a product microservice which stores drugs and we have an inventory microservice that stores inventory for that product. inventory id productid quantity on hand 1 1 10 2 2 10 3 3 10 One of ...
0
votes
3answers
54 views

Is it better practice to have a column which is null part of the time, or a separate table with a foreign key?

Let's say you have a table, subject, which represents a test subject (a chemical). The testing will provide a result, pass, fail, or inconclusive. It will spend approximately 75% of it's time not-null,...
0
votes
0answers
16 views

Appropriate way to convert Extended ER diagram to Hibernate Entity and repository

I am new to Hibernate and don't know well how to convert EER to JPA entity and repository. Would you demonstrate how to create Hibernate/Jpa entity and repositories from this diagram . You can ...
0
votes
0answers
25 views

ER Diagram Aggregate relationship

Recently I started working on a class project where we are required to make a DBMS that models a game recommendation engine. We just got the ER diagram started, & I ended up making an aggregate ...
0
votes
1answer
62 views

SQL case problem [duplicate]

SELECT CASE WHEN OfferSum.DayOfWeek = 'Monday' THEN 'Mon' WHEN OfferSum.DayOfWeek = 'Tuesday' THEN 'Tues' WHEN OfferSum.DayOfWeek = 'Wednesday' THEN 'Wed' WHEN ...
0
votes
0answers
43 views

What is Post-Relational data model?

I searched in google for "what is Post-Relational data model", but I couldn't find an exact answer. so, what is Post-Relational data model in databases and what are Post-Relational databases?...
0
votes
0answers
15 views

Bucardo Race Condition

I have an application that uses multiple PostgreSQL databases and Bucardo to sync between the various master/slave nodes. I'm seeing a race condition where bucardo dies on a master node and doesn't ...
0
votes
1answer
43 views

Entity-relationship for database design

Customers have the option to not provide a review or leave as many reviews as they want, but they cant have multiple reviews for the same product. How do I model this?
0
votes
0answers
35 views

Database based on nesting JSON

I currently am storing raw JSON in a table as I cannot figure out how to create a table structure for the data I am using. The JSON example I have is: { "type": "HEADER", "...
0
votes
1answer
26 views

How to re-design my relational model if there will be NULLs in one column?

I'm trying to build database which allow me store job postings from scraped websites and now I'm in phase of creating a relational model. Getting straight to the point I want to know how should I re-...
1
vote
1answer
38 views

What are the reasons to have an API back end if everything required can be handled securely by direct Database connection?

Today's database allow strict user restrictions, such as readonly on specific tables. If a client application is very simple and only needs to read a database, is it a better idea to allow the client ...
0
votes
0answers
18 views

Showing a different set of fields for different rows

In my Postgres database I have a table of employees: CREATE TABLE employee ( id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY name text, email text, phone text, department text )...
1
vote
0answers
30 views

What data modelling tool is sufficient for a student learning database design?

Disclaimer: I do not intend this to be a question based on opinion and start data wars (I don't know if such things exist in this realm unlike software development and programming languages) What I ...
0
votes
0answers
18 views

User Table - Good Design w/ Request Rate Limit?

I would like to implement request rate limiting on my http server, for example for the "Forgot Password" form. I would like to allow 1 request per 10 minutes, for example. For this, I would ...
0
votes
1answer
42 views

How to design & implement folders and subfolders (infinite) in my database?

So I am building a desktop application using Qt, C++, and SQLite. I want the user to be able to create as many folders as he likes, and those folders can have "notes" (its a note-taking app) ...
0
votes
0answers
15 views

How would you graphically represent a relation between an associative entity and a subtype entity?

I need clarification and assistance in converting the following EER diagram to a relational schema, particularly the relationship between PERFORMANCE, an associative entity for PLAYER and MOVIE, and ...
0
votes
1answer
36 views

Do I need to implement id column to table if i know that every added row will be unique? [duplicate]

I want to create database which allow me to store IT job offers from various websites. While creating ER diagram I started wondering if I really need an id column in one particular table. Look at this ...
0
votes
0answers
25 views

How to get all the extended properties inheritance and regarding this heritage apply the values of extended properties?

Hope you are well I'm been a bit stuck trying to develop the code to get the inheritance of extended properties. I will explain the situation: The thing is have a query to list all extended properties ...
0
votes
2answers
57 views

Is it true or just a myth that executing SQL Codes in the Database is the fastest in the entire web based application?

My Manager, who has been a DBA for like 20 years, told me that I should make maximum use of a database , because it is the fastest component in a web server. So, if I am using FrontEnd, PHP (...
0
votes
0answers
38 views

Need feedback on my ER diagram

I want to create a database which allow me to store IT job offers scraped from several various websites. The problem is that job offers from these websites are not described in the same consistent ...
0
votes
1answer
25 views

Add an auto now field to an existing table

I want to add a new column created_datetime. This column will automatically be filled with a the current date/time value when inserting a new row. How should I handle the value for existing rows. ...
-2
votes
1answer
70 views

Relational database with Kafka-like durability/caching implementation

When reading about Kafka I found it really interesting how they are providing durability, especially in comparison to databases: Kafka - never writes synchronously to disk, provides durability by ...
0
votes
1answer
59 views

Does putting constraints decrease the required size to store my data in SQL Server?

I would like to know if putting check constraints decrease the required column size or not? For example, when I create an int column and a check constraint on it to store the numbers between 100 and ...
0
votes
1answer
34 views

Best practise to compute total amount from individual entries

I want to keep track of the individual and total amount of drinks someone ordered. Table 1 Customer information: customer_id name age 1 Steve 44 4 John 24 Table 2 Drink ...
1
vote
1answer
22 views

MySQL query show something like 0x8081 instead of special characters

When i run SELECT CHAR(128,129,130,131,132,133,134,135,136,137); I got 0x80818283848586878889 istead of Çüéâäàåçêë. Does anybody know why?, I'm using charset utf8mb4. When I run show variables like '%...
0
votes
2answers
22 views

How to organize MongoDB replicaset backup and alerting

I have a running MongoDb replicaset consisting of 2 replicas and 1 master nodes. Recently I noticed that my master gone and automatic failover took action and changed master, however I wasn't aware of ...
0
votes
0answers
42 views

Maintaining multiple data providers and migration approaches in SQL

Consider the synchronization of Basketball matches from an external provider. For this, I have two tables, the match table: +----+----------------------+-----------+-----------+ | id | date ...
0
votes
0answers
17 views

RDB data combination table modeling

There's data and if I combine it, I need to get the corresponding result. If I choose A and I got B, If I choose B, I should also get A. I want to save both data and combinations in DB, but it's hard ...
0
votes
1answer
47 views

ERD for yearly budget ceiling advice

I'm working on webdev that involving yearly budget ceiling, but I can't wrap my head around to design such budget ceiling (my first project involving budgeting). So I need some advice. So the scenario ...
2
votes
1answer
91 views

How to Name OurCustomerNumber and TheirCustomerNumber

All business partners are stored in one table and can be customers, suppliers or both. Now I need to store the customer number the business partner has assigend to our company ("their customer ...
0
votes
2answers
41 views

Which way is a better structure for users table in a shopping project - SQL Server

I think it will be faster, but if its a better database design and structure I'm not sure, Should I create a field in Users table which indicates if the user has a shopping cart or not, and only if ...
0
votes
0answers
15 views

Worksheet keeps on loading after refresh in Demantra

Oracle has suggested doing a data refresh to check on the issue of “Unable to save the data” which we are facing in the DEV instance. We thought the issue is due to the Demantra web generic patch ...
1
vote
0answers
60 views

how to delete or drop database when I missing physical data

I have database with name music I can Not work on database I can not drop my database ,because I Get this Error (dbcc checkdb) The operating system returned error 21(The device is not ready.) to SQL ...
0
votes
1answer
8 views

Save changes of records of products in Azure DB in way that we can see historical data

I am curious about "best" model for my purpose of storage of data (include historical changes) in Azure DB that I will be able to see timeline of that changes. Example: We have some company ...
0
votes
3answers
78 views

Storing huge file collection in database

I'm in a planning phase of developing a web site where public users will be able to store their documents, by nature it could be anything like documents, pdf, audio, video or images. We are expecting ...
0
votes
0answers
13 views

How to structure SQL database for multiple types of users and roles

I'm building a db for a site where brands can create a storefront and also create staff accounts to manage said storefront. They'll also be customers who aren't associated with a brand whatsoever that ...
2
votes
2answers
88 views

Keep full history of every values in each table

How would you do to keep full history of every values of all tables in a DB, which can be queried with a specific date (for each and every column of all tables)? I mean: one would just need to specify ...
-1
votes
1answer
40 views

ClickHouse MV is not working perfectly as i need [closed]

I’m new to ClickHouse and having an issue with MV. I have a record table which is the data source. I’m inserting all the data here. Then created another table called adv_general_report using ...
0
votes
0answers
14 views

Weak entity of weak entity

I wonder whether it is possible for a weak entity to have another weak entity dependent on it. Specifically, I have a strong entity QUIZ which comprises of QUESTIONs unique to that QUIZ that I have ...
0
votes
0answers
31 views

document db tree structure - is it valid to have both child and parent references in each node?

I'm in the process of building a tree structure, which should support the following requirements: It should be possible to find the root of the tree The tree will most often be traversed from root-&...
1
vote
1answer
43 views

Database diagram for school management app

I am currently making a management software for a small artistic school: a small intranet in PHP / MySQL. I'm trying to draw database architecture before I start coding because I don't want to make ...
1
vote
0answers
85 views

How to optimize for performance when querying millions of rows in real-time analytics?

Suppose I have a simple table called events like this: timestamp event_id action_count apr 5 01:05:00 foo 10 apr 5 01:04:15 bar 20 apr 5 01:06:10 baz 200 apr 5 01:04:30 baz 5 apr 5 01:03:00 baz ...