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
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. ...

1 2 3 4 5 102