Questions tagged [database-design]
The development of the conceptual schema and/or the logical model and/or the physical settings of a database.
5,792
questions
0
votes
1answer
22 views
Database schema for marketplace[posts_attributes]
Okay so I am making a marketplace type of website like olx etc.. users can post different types of posts meaning the fields change so I made an extra table called posts_attributes which those changing ...
0
votes
0answers
34 views
How I can handle the records?
In fact when I explore mysql then I insert a new record to table
INSERT INTO cats (name, owner, birth)
VALUE
('SMSM','lennon','2015-01-03');
First I have an id int auto_increment so I don't wrote ...
0
votes
0answers
31 views
MySQL complex famiy tree query
I'm trying to create a query that will return a full family tree by ID.
The goal is to get a table that contains all of the ID's family ties.
I want based on the data I have to write a procedure / ...
0
votes
1answer
27 views
Correct database schema?
A system for a bike repair shop has 2 types of users: Mechanics and their Customers.
The schema has this structure:
Table Users:
The boolean field is_staff defines the type of User (if is_staff is ...
-2
votes
0answers
38 views
How to solve " database: syntax error at or near "(" " [closed]
DROP TABLE IF EXISTS Product;
DROP TABLE IF EXISTS WaitingList;
DROP TABLE IF EXISTS GiftCards;
CREATE TABLE Product (
P_PluID NUMERIC(13) NOT NULL,
QueueID NUMERIC(5),
...
-1
votes
1answer
41 views
Suggestions to create a table or to design a stored procedure to remove while loops
The company I am currently working for, has a scenario for which we have to use while loops or cursors in SQL. Which has a huge performance impact in our case, hence, I want to optimize it.
The ...
0
votes
0answers
41 views
Avoiding Conversions (or an EAV model)
I was given the task to turn some really messy data into a database. Coming from an Stats/R/Data Science background, I took Hadley Wickham's philosophy of tidy data as a starting point.
However, when ...
-1
votes
0answers
24 views
How to efficiently store duplicate key-value pairs
I'm not sure that this is the right StackExchange site for my question, so please redirect me if needed.
My data is in this format:
{
"name": "Connection1",
"...
-5
votes
0answers
22 views
1 Database per customer, or one database for all customers? [closed]
What will be faster?
Creating 1 database for 10 companies, or creating 1 database for each company?
Faster means Better User Experience.
1
vote
1answer
48 views
What's the best practices for storing visits?
I would like to save visits in a table. I am using MySQL but I am not sure how I should save the visits as the records may increase considerably and this might cause performance issues and I am also ...
0
votes
0answers
13 views
Please help me figure out the structure of the ecommerce store database
I'm doing a study project (online smartphone store) in python + flask + postgresql. I'm still new to this and would like to ask for help with the db structure. I made a rough diagram. Can you tell me ...
-1
votes
1answer
33 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 ...
0
votes
1answer
40 views
Writing DBMS for accounting
As a learning project to solidify my knowledge about computer science I want to write a DBMS for accounting (I majored in accounting) since it would prove to be challenging and enable me to practice ...
1
vote
0answers
37 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 ...
-1
votes
0answers
23 views
Efficient way to lookup values in a column?
Supposed I have a few dimensions and values e.g. country_code (UK, US, IE) and region (US, EMEA, Asia).
I want to scan an input table (assume 1 billion rows of data) with 'x' columns and identify the ...
0
votes
0answers
38 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
48 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.
...
-2
votes
0answers
22 views
Laravel MTG DB Design
I'm pretty new to laravel and DB design in general, and I'm creating a card database for Magic the Gathering. I'm having trouble figuring out how to create the tables as they relate to legality in a ...
0
votes
1answer
32 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
42 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
52 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
46 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 ...
-1
votes
0answers
38 views
Opensource database for store big boolean list with only be added data
I am looking for a opensource database for store big boolean list.
Requirements:
Operation of probes: union, intersection.
The value of probes will not be updated.
New probes will only be added.
...
0
votes
1answer
41 views
database design of super and sub types
I system requirements I have are as follows: In my app,as far as person entities, let's say I have users, adults, children, parents, friends, artists, customers, businesses, employees, event ...
0
votes
3answers
52 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
14 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
20 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
61 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 ...
-1
votes
0answers
8 views
Looking for a better memory level faster database/Data ware house
Looking for a better memory level faster database/Dataware house Eg:(Redshift vs Exasol )
Better query performance for advanced complex queries (large data set 10 GB to 50 GB).
Scalable
Secure
...
0
votes
0answers
41 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
13 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
41 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?
-1
votes
0answers
47 views
Can somebody please explain me the relations in this table?
I am new to this Database concepts and i have been asked to create tables for this. It would be much helpful if I know the how to find the mappings!
Thanks in advance guys!
0
votes
0answers
34 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
36 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
41 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
24 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
53 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
37 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
23 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
33 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 ...
