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
0
votes
0answers
11 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
12 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
20 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
12 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
17 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
16 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
36 views

Store everything in one table or multiple tables

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
23 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
10 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
0answers
29 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
43 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
22 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
30 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
1answer
38 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
19 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
41 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
58 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
25 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
votes
0answers
17 views

How to Calculate the percentage of total arrests for 2015 in Arizona, and name the calculated field ‘2015_pct_of_total’? Thanks! [closed]

How to Calculate the percentage of total arrests for 2015 in Arizona, and name the calculated field ‘2015_pct_of_total’? Thanks]1
0
votes
0answers
16 views

database structure for ecommerce (laravel) [closed]

I'm creating an E-Commerce website. and I'm stuck with the shopping cart and payments and orders ... I don't know how to build a reliable and safe structure. you can see my attempt in this picture: ...
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
47 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
44 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
66 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
28 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 ...

1 2 3 4 5 103