Questions tagged [database-design]
The development of the conceptual schema and/or the logical model and/or the physical settings of a database.
5,105
questions
0
votes
1answer
20 views
Better DB design to have a SUM column, or to constantly add up all relationship items per query?
I have a database with an invoices table and invoice_items table. For simplicity, let's just say theinvoices table has the following structure:
| id | name | created_at |
The invoice_items has this ...
0
votes
0answers
21 views
MySQL capability become insufficient
My ex-colleague deployed a MySQL database on Alibaba Cloud Elastic compute service (ECS) instance. I have taken over for 2 months. Due to traffic increase, the service capability (concurrent ...
0
votes
0answers
20 views
Storing Large Quantities of Time Series
What is the recommended (type) of database design for storing large amounts of time-series data?
The dataset:
2 million time-series;
Each of the time-series contains around 500 timestamps;
At ...
0
votes
0answers
26 views
Database design of a simple teach-kids-to-read web-app
The question is about my database design. Is it OK?
I feel uncomfortable about having 2 separate WordTraining and SyllableTraining tables and about too simple design (not much normalized). Maybe ...
0
votes
0answers
19 views
Fastest way to make full text searches through 2 columns?
I actually have 200 GB of data indexed on MongoDB, spread over 2 columns, each containing one string.
The user search for 'cowboy', if this string is found anywhere, it returns the content.
i.e: ...
0
votes
1answer
20 views
When should I extract a field into a new table?
Given a table :
CAR
---
id
model_id
condition
fuel_id
release_year
Since many cars would have the same Model and same Fuel, there are 1:N tables created.
Though, many Car objects can have the same ...
1
vote
1answer
20 views
Circular reference and multi-tenancy
I'm have an issue with circular reference in my DB design. As see the ER diagram below have 2 circular reference that I highlighted with Red and Orange arrow.
Anyone know how to resolve this issue?
0
votes
0answers
15 views
Storing type in each table or give item a type
I need to design schema for a project that involves two things.
1. providing services ( realted to vechicles)
2. Also sells insurance and spare parts
Below is the schema that I have designed so far.
...
0
votes
0answers
39 views
Store everything in one table or multiple tables [closed]
Possible duplicate of Storing data in PostgreSQL: One table or two? and probably to broad question.
But anyway.. Im a noob in general at database administration. I want to weekly copy data from mssql ...
0
votes
1answer
24 views
Delete all data for a Single Vendor in the Dabatase
I have a Vendor table with vendor_Id as the PK and 10 other tables which have reference to Vendor.vendor_Id.
Now, if Vendor chooses to close their account, we need to delete all the data in the other ...
0
votes
0answers
11 views
Can a weak entity have relationship with multiple strong entities?
I ran into a situation where a weak entity happens to have attributes which are primary keys in two different entities.
My situation is like:
Strong entities:
--Course
-Course_Num(P.K)
-Title
-...
-2
votes
0answers
21 views
In database what is the role of primary key? [duplicate]
We all know the keys are play an important role in Database management system ,So I want to know the meaning of Primark key.
0
votes
1answer
47 views
+50
Unrelated tables and time intervals
I am going to comment on the scenario that I currently have when designing my database.
I have the following tables:
user (id, name, lastname)
role (id, name)
user_role (user_id, role_id, ...
0
votes
3answers
45 views
How to store real-time data to database [closed]
Here is my situation:
I have a WebSocket connection that feeds me data real-time
I have an async callback function that gets the data and inserts into a queue
I have another thread that reads from ...
0
votes
1answer
24 views
I have account_id on all of the tables in the database that belong to an account. Could this be why our database is using a lot of memory?
The application makes heavy use of data. It is a Real Estate Lead Management System each lead could make our users thousands of dollars, and accidentally returning other people's leads would be a sure-...
0
votes
0answers
32 views
Best Practice: Persisted Computed vs Computed with Index vs?
I am an accidental DBA charged with performance tuning a mess of queries (both literal, and figurative).
One pattern we have is to always store Dates as DateTime2(7) regardless whether we need the ...
0
votes
2answers
47 views
Approaching database design
I have 1000 sensors, and I need to hoard the value of each sensor at every second, for a month. This is just theory, the most realistically achievable unit of time will be determined when I start ...
1
vote
1answer
39 views
minimum space is required on disk to restore a backup in SQl Server?
Suppose I am going to restore a backup (2 GB) in my sql server, I have sufficient space (20 GB) in my disk, but is there any algorithm that SQL server follows to calculate the minimum disk space ...
0
votes
4answers
41 views
It's possible to order alphabetically all columns in tables of a mysql db?
It's a large db and it's really messy, I would like to order all columns in all tables, without resorting to manual drag and drop...
EDIT
I know the order doesn't matter. It's a design think. It will ...
0
votes
0answers
20 views
anonymise customer orders [closed]
In my db I have customers with theirs orders.
All orders older than 3 years must be "anonymous", but I still need them for statistical reasons (that could include, nationality, age, number of orders ...
0
votes
1answer
45 views
Select rows from table having more than two specific values
I have a table having details such that
Userpin product Time1 contains
4556 23 7:30 A
4557 23 7:30 B
4558 23 7:30 C
...
0
votes
2answers
66 views
Database Design for Pokémon Review
I am trying to learn more about Relational Databases. So, I did a pokemon database gathering the stats available on the Smogon website. I have done some research thus far and would like to get some ...
0
votes
1answer
15 views
Single Column index or 3 column index in MS Access
I am creating a table with a single memo field and either 3 text fields (tablename, fieldname, caseID) or a single text field with the 3 text strings concatenated.
There will be a single NO ...
0
votes
0answers
8 views
Separate MongoDB Table Server got down
This issue is very new for me.
This scenario is about Production as per I have kept some large tables on other server.
for a month The Server was going smooth but after one month it got down ...
0
votes
1answer
17 views
Select rows from table having specific values depending on another values in the same table
I need to be able to select all Userpin that have tr_type 7 only but if there is tr_type 7 and 8 in other row ignore 7 and 8 example:
Userpin TrDate Time1 Tr_type
4555 12-02-2020 ...
0
votes
2answers
37 views
Referencing two documents to each other
Is it good practice and or recommended to reference 2 documents to each other using ref? Like this:
const FirstDocumentId = mongoose.model(
'FirstDocumentId',
new mongoose.Schema({
...
0
votes
0answers
21 views
Best practice guidelines for a application table archive in Oracle 12C
Database environment: Oracle 12C Enterprise edition
I have a table in the application schema and corresponding history table in the history schema.
The application table has about 10 small varchar2 ...
0
votes
0answers
26 views
Is it practical to use entity-attribute-value (EAV) model to support custom user fields and avoid bloating of tables?
I am currently working on a medium-sized web app project that is already in production using Laravel framework and MySQL 5.6.40. My clients have been asking for the ability to add additional ...
0
votes
1answer
19 views
Convert rows into columns MySQL DataBase
I have two tables and I would like to generate a query relating the tables.
Until now I have this query:
SELECT Table1.*, table2.* FROM Table1 INNER JOIN table2 ON table2.FK_ID = Table1.ID
but I ...
0
votes
0answers
20 views
Storing common data instead of multiple joins
I'm wondering if it's a data modeling strategy to store data in a retrieval table (not sure if that's a thing).
I have 5-6 joins going through 10,000s of records that is only going to get more ...
0
votes
2answers
31 views
Split database per pool [closed]
I need to split a database of users by X pool and each pool need to have nearly the same number of users based on their email provider...
For explain to you better, if my database content:
100 users ...
1
vote
1answer
33 views
Two substantially different but similar cases: Are they the same in terms of table cadinality?
Imagine these different cases when designing a database:
Case 1:
One book (only one instance of a title physically) can be borrowed by many people, but only one person at once but many people during ...
0
votes
0answers
19 views
How do I set up a report that shows number of clients by age group when their age is stored by date of birth in Microsoft Access
I am working on setting up a new database in Microsoft Access for a food locker it will be used for client input as well as tracking client visits. One of the monthly reports they need is how many ...
0
votes
1answer
22 views
Manage Postgres user permissions with many databases and users
How do you manage users' permissions efficiently when your company has a lot of databases and users?
My company has > 20 reporting databases. Whenever I need to change permission for some users, I ...
0
votes
1answer
18 views
Filemaker Database. How to import to one table then copy information to a different table and populate realted tables
I'm using filemaker to create a union membership database, the problem i'm having is that you can't import files into related tables. Basically i'm trying to figure out how I could import an excel ...
1
vote
1answer
48 views
Database Design: Normalization and NULL values
I have normalized my table from 0NF to 2NF (hopefully correct).
How do I now go from 2NF to 3NF?
Also, ResponseToTransactionId is a recursive foreign key that will contain NULL values a lot of the ...
1
vote
1answer
29 views
ER Diagram Design: Should every entity have a primary key? And can we have sub entity?
Problem: Design a ER diagram such as:
An item has the attribute: description.
An item can be sold by a company or a person.
A person has the attributes: name, phone and email.
A company has the ...
0
votes
1answer
22 views
Database for subscription system with multiple opt-in criteria [closed]
I want to create a subscription system for app users.
I guess I need at least two different tables:
1) One table will contain the user preference for how often does he wish to get an update, for ...
2
votes
1answer
28 views
Is creating a joining/bridging table the most practical and efficient way of normalizing numerous M:M relationships in a database?
Let me start with an example:
Table users:
ID | Name
---------
1, Kirk
2, John
Table class:
ID | Class
----------
1, MATH
2, FIN
Now, based on what I studied so far, in order to properly ...
0
votes
1answer
25 views
How do I handle a database for a form who's structure will likely change in the future?
I need to build a database to record form data from a custom tool. This form consists of a fairly basic set of checkbox/radio button fields so the data itself doesn't present much of a problem.
User ...
0
votes
1answer
22 views
Data Lake : Data Catalog and Data Schema definitions
I'm trying to understand the definition of data catalog and data schema. After several articles I read, I'm getting even more confused!
I realized when the article is talking about data lake, the ...
0
votes
0answers
19 views
How to design e-commerce database for a product which has an option what time to get
I'm designing an e-commerce website.
It has a product that have options to choose for a customer.
the options that a customer can select are what time to receive the product today.
-------------
| ...
0
votes
1answer
45 views
Query that marries data between two databases
Database 1 is the master database where data on all domains in our client's industry are stored
Database 2 (or Database N) are the application specific databases of our client. (e.g. Application ABC ...
1
vote
3answers
67 views
Is it ok to add a table years in a Relational DataBase?
How can i explain my coworker that this is unnecessary complexity and redundant data?
He wants to have a table of years because many tables have the 'year' value, and he also want to have relation ...
-1
votes
0answers
43 views
Are repeating rows the same as repeating groups? (1NF)
Is this a violation of 1NF? Are those repeating groups?
+--------+----------+--------------+-------------+
| ItemId | ItemName | ItemCategory | Department |
+--------+----------+--------------+------...
0
votes
1answer
29 views
ER Diagram about Music Online Store
I am attempting to make an ER diagram where the customer buys music online. We were just in the first weeks of tackling database so a lot of topics isn't introduced yet. Here's what I've done so far:
...
1
vote
1answer
50 views
Is it realistic to expect a database of size over 50 GB (some 300+ million records) to be imported within 3-4 hours on a single server (MySQL)?
I have hundreds of millions of rows in a text/csv file (genomics database btw - each record is less than 255 characters long...).
Ideally I'd like to make them searchable since right now my best bet ...
0
votes
3answers
23 views
How many bytes does character takes if I created database with utf8 in mysql
Suppose if create a database with utf8 character set and created table and inserted data.
Here my doubt is if the character is 1 byte then it will occupy only 1 byte or it will occupy 3 bytes ...
1
vote
0answers
30 views
SKU and Price on product that have multiple attributes
Currently, my Inventory system is writing products data like this:
T-Shirt Gildan -> blue, L, short -> stock: 140 -> price: $40
T-Shirt Gildan -> blue, XL, short -> stock: 0 -> price: $45
T-Shirt ...
0
votes
1answer
24 views
Add constraint on number of entries of certain type in referenced table
As a personal project I am working on a database to keep track of cars as they are manufactured and then sent to dealerships. One of my constraints is that certain dealerships can only have a certain ...
