Questions tagged [database-design]
The development of the conceptual schema and/or the logical model and/or the physical settings of a database.
5,097
questions
0
votes
0answers
12 views
Relationship between tables with period table
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
26 views
How to store real-time data to database
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
19 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
12 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
25 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
38 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
37 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
36 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
1answer
31 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
7 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
16 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
34 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
20 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
21 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
15 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
44 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
28 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
18 views
Database for subscription system with multiple opt-in criteria
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
27 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
43 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
65 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
27 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
48 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 ...
0
votes
0answers
22 views
Use nullable fields to choice the behavior of Controller
My simple ERP provides three ways to add an item to an invoice:
a row from a delivery note
a row from an order
a custom entry
Because for all three cases I may need to display an order reference, I'...
0
votes
1answer
19 views
Updates since the previous commit point Vs updates prior to the commit point
The quote below is from An Introduction to Database Systems, 8th edition, C J Date.
I am confused since I think "all updates since the previous commit point" is the same as "updates Prior to the ...
0
votes
0answers
15 views
Identifying Functional Dependencies what's the technique
Is there a simple step-by-step tutorial or some other page that can guide and teach how to identify "FDs" or "FFDs" in a table or among many tables in a DB schema design???
-1
votes
0answers
40 views
Proper Way To Handle User Level Credentials
For the purposes of having users login into an external app and handling a password during that process, what are currently considered the proper way to manage user level credentials and secure data ...
0
votes
3answers
44 views
adding cluster index other than primary key default cluster index
create table t1 (id int primary key auto_increment, name varchar(20))
In the above table id is primary key and by default cluster index is added to primary key column.
My question is, can we add ...
0
votes
1answer
31 views
Design ER with complicated relationships between different tables
I try to design a database and I am stuck dealing with a part of it. The problem is very scientific, so for simplicity, I will try to give you a simple representation that accurately matches the case.
...
1
vote
0answers
34 views
Correct states in database
The following quote is from Database by C J Date.
consistency does not imply correctness. Sadly, therefore, all we can do and all the DBMS can do-is simply assume that transactions are correct, in ...
0
votes
0answers
37 views
How should I design a multi-purpose table?
Let's suppose I have the following schema:
Table A has a 0 to N relationship with table B
Table B has a 0 to N relationship with table C
Each item on A, B or C may have comments from multiple users (...
2
votes
3answers
51 views
Table Creation Order
I have designed my database model with entities and they have relations along with normalization. Now I want to implement my design in my database engine. Before implementing I am puzzled with the ...
0
votes
2answers
36 views
How to give a table a “primary selection” to a table it is many-to-many with?
Description of Issue / Question
I want to be able to specify a default or primary relationship of a table to a row in the database that the first table already has a many-to-many relationship with.
...
