Questions tagged [normalization]
Normalization is the process of organizing columns into tables within a relational database in such a way as to minimize redundancy and avoid insertion, update and deletion anomalies.
662
questions
1
vote
1
answer
67
views
Database design, one to one relation split into multiple tables
Let's assume we have following database:
Or in pseudo sql format (all columns are not null):
create table road (
id int PK
)
create table item_on_road (
id int PK
road_id int FK road.id
...
- 135
-1
votes
0
answers
38
views
what violates the first normalization rule
I was convinced that what violated the first normation rule was having a multivalued column.
id name friends
1 'john' 'Maycon, Gabriel'
That has multiple names in the same column.
Compound ...
- 119
0
votes
2
answers
56
views
Correct (as in normalized) way to express a relationship between 3 tables with complex(?) constraints?
I'll be using Postgres for the examples, but feel free to show examples in other databases if needed.
The simplified schema:
create table factory (
id serial primary key,
detail text not null
);
...
- 11
-1
votes
1
answer
12
views
How to map question to chapters in a examination system?
I am going to create an multiple choice based examination system. I am new to database design and want your help to answer a few questions.
How to model questions and questions category?
Context:
...
- 101
1
vote
0
answers
84
views
Which is the best way to model different types of people and relationships in a database?
I have a scenario where I have two tables, person_natural and person_legal.
Case 1:
I have employees, farmers, customers, and suppliers. Should I have an enum in each table (person_natural or ...
- 19
-1
votes
1
answer
34
views
What normalization rules flattening one-to-many violate?
I'm trying to better understand NF in a theoretical sense. The data I have feels wrong but I'm not sure if/how it violates normalization
Example table:
product
inc_charger
inc_cable
inc_headphones
A
...
- 101
0
votes
0
answers
25
views
Design for massive dimensional table to improve query time? Can it be partitioned horizontally?
I've never had this issue before -- but inheriting a big data project.
There is a dimensional table "account" or "user" that contains every dimensions known to mankind. Or let's ...
- 1,651
0
votes
1
answer
73
views
Database design - normalisation query
I am a teacher and am querying a solution given by our examination board. The question is shown below and I'd like to ask for help in understanding why my solution would not be acceptable.
Here's the ...
- 103
-1
votes
1
answer
39
views
Design and normalization issue : type of FK depends of an other field
I have a CapitalIncrease table, which contains a list of available payment methods. For each payment method, I need to put some data (SepaDocument OR Iban OR Crypto).
Here is a mermaidjs graphic of ...
- 1
0
votes
1
answer
31
views
Are functional depencies with boolean conditions a thing?
Suppose we have a relation R(A, B, C) with the FD A -> B. Let's say that, for two tuples t and u in R, if both t[C] and u[C] satisfy a boolean condition - say less than a constant C - then t[B] = u[...
3
votes
0
answers
59
views
Best practices for heavily coded data in a relational database and lookup tables
I'm working on converting a legacy system into a more modern web application and I keep finding myself tripping over normalizing the data.
The dataset is quite large; about 30 tables with around 15 ...
- 31
0
votes
1
answer
44
views
Normalisation with an Underlying Hierarchy
I'm designing a new database schema, and every time I do, I like to check my assumptions on normalisation. Something I haven't been able to find a definitive answer for here or by searching the web is ...
- 117
0
votes
0
answers
39
views
3NF Decomposition and NP-Completeness
I have a question about the computational complexity of the algorithm that allows us to decompose a schema into 3NF.
My book says that the decisional problem of telling if a schema complies with the ...
-2
votes
2
answers
63
views
Why is it a bad idea to have multiple values in a column in a relational database?
One problem I've found is slow database queries. If I had a table that had a column titled customer and another column titled grocery_items that contained multiple grocery items, then a simple query ...
0
votes
1
answer
46
views
Overkill or good application of normalization to create parent table just to associate a collection of children
I have a situation where I need to keep a group of detail rows together but where the "parent" has no additional attributes and the "children" have no purpose on their own. I ...
- 103
0
votes
0
answers
55
views
Functional Dependencies and Armstrong notation fundementals [duplicate]
if AB -> C and B -> A does that mean B -> C?
B -> B is trivial and if we do composition with B->A, it becomes (B-> BA), then by transitivity does that mean B->C? but if B -> C ...
0
votes
1
answer
294
views
Correct entity relationship diagram representation of a normalized table with a NOT NULL foreign key
Intro
Hi everyone, this is mainly a classification/theoretical question on the topic of inheritance and normalization techniques in database design and their appropriate representation in entity ...
0
votes
0
answers
75
views
Why is the relation in 3NF?
A relation is in 3NF (Third Normal Form) if it is in 2NF (Second Normal Form) and all the FDs (functional dependencies) in the relation are in the form X -> Y, where X is a superkey (a set of ...
- 101
1
vote
2
answers
99
views
Help - Database design
We are implementing the new requirement in our current web application. If we discuss the database design for the new requirement, it appears to be more complex.
Requirement -
We have two webforms; ...
- 165
0
votes
0
answers
125
views
Foreign Keys As Composite Primary key
Can two non-key attributes of the same table be the composite primary key of another table??
In relation R(BCFGH) following set of FDs hold:
B -> CFH
CH -> G
Since CH -> G is transitive ...
2
votes
0
answers
44
views
For selling items in a game, should the shop and item tables be merged?
I'm developing a game that uses a Postgres database. In the game players can have many different items, and each player can have multiples of an item (example: 200 apples). My item table currently ...
- 121
3
votes
2
answers
61
views
Any benefit in replacing a varchar for an index in the main table?
I'm 'inherithing' the job of a database admin for a very small team (3-4 people) and while I know how to execute SQL queries relatively well and understand the basics of programming efficiency , I ...
- 133
0
votes
3
answers
763
views
How do I handle an INSERT to 2 tables within a transaction?
I have 3 tables in a Postgres DB:
UserType -> ID, creation, delete, update, name
User -> ID, creation, deleted, update, user_type_id
Employee -> ID, creation, deleted, update, user_id (fk)
...
- 1
3
votes
1
answer
45
views
Do "range" data types in PostgreSQL correspond to First Normal Form?
I have a task for my university project to confirm that the database I made is in Third Normal Form. I decided to use the "daterange" data type for one of the columns in my database, which ...
- 33
0
votes
1
answer
76
views
Is a "static" FK bad design? [duplicate]
I have a table table1 where records can have different status. If status=10 I need to store more information. In order to keep the db normalised I'm storing the extra information in a separate table, ...
- 117
0
votes
1
answer
184
views
Many to many relationship when join table takes two same entities and an additional one
as of right now I'm trying to design a database which contains data involving football matches. I have two tables: Match & Football Team both containing various attributes. These are connected via ...
- 63
2
votes
0
answers
47
views
One-to-One? or One-to-Zero-or-One?
Scenario: I am being asked to create an HR training tracker for employees of a business. in this database I am using two tables:
EMPLOYEE(EMPLID, LastName, FirstName, PrimaryEmail) with EMPLID as the ...
3
votes
2
answers
768
views
can two tables have same primary key and at the same time this primary key is a foreign key for one of them?
I have a table
user
id (PK)
email
username
password
status
user_role
profile_image
created_at
and another table:
healthdata
id (PK)
age
gender
height
goal
weight
...
- 39
-4
votes
1
answer
37
views
Is it normal to have 300 queries to display a single page? [closed]
The total number of queries is: Q1 * Q2 * Q3, it might be 25 * 3 * 4 = 300, or maybe even more.
all of these is for a single user displaying a single page!
Should I duplicate the data and store them ...
- 1
-1
votes
1
answer
74
views
Help with tables normalization for delivery system
i have a task to design a SQL database to represent a "delivery". I've been able to solve most of the needs, however i have doubts with one relationship:
A delivery has an origin and a ...
- 1
-1
votes
1
answer
57
views
Why aren't normal forms defined more simply?
I believe that 2nd and 3rd normal form definitions could obviously be specified with less complexity, but they're not.
The definition found in textbooks is something like this:
2NF: No partial FD ...
- 9
1
vote
1
answer
1k
views
Set into multiple rows [duplicate]
I have the following data
SQL> SELECT id, relatedIdInASet from tab;
+----+------------------+
| id | relatedIdInASet |
+----+------------------+
| 1 | [12,34,56] |
| 2 | [11,12,22,34] |...
- 513
1
vote
1
answer
79
views
A clarification about data normalization
I've been a software developer for 8+ years now and I think I have a pretty good grasp of the concepts but as an entirely self-taught developer, I have some unfortunate gaps in proper terminology. ...
- 111
0
votes
1
answer
116
views
Normalizing an old database
I have a need to normalize an old PostgreSQL database, and I have an idea on how to do it which I would like to hear your opinions about.
The aspect that needs to be fixed is this:
We have a consumer ...
- 101
-1
votes
2
answers
3k
views
How to design to avoid duplicate Foreign keys
I wasn't really sure how to title this question. I have three tables User, Photo and Album. A user has many Photo objects and many Album objects. However, after a User has added a given photo they may ...
- 101
0
votes
1
answer
606
views
Is there a dependency preserving, lossless BCNF decomposition for this relational schema?
R(A,B,C) where {AB -> C , C -> A}. The candidate keys are {A,B} and {C,B}. This is in 3NF but not BCNF because of {C->A} . Now it seems like this can't even be split into a lossless BCNF, let ...
- 107
-2
votes
1
answer
167
views
Normalize to 1NF and 2NF, also 3NF (if possible)
I have a sample table with the dataset unnormalized, So I need to make it normalize in 1NF and 2NF and answer 5 different questions from those tables.
Part of my dataset: (unnormalized)
category |item ...
1
vote
2
answers
184
views
Separate table to store long string identifiers
In my postgres DB, have an assets and a sales table. The current owner of an asset is identified by an Ethereum address with 42 characters, e.g. 0x81238432CE9b51Fbc2b59AFD74Ba8D09863B8B00. A sale has ...
- 111
1
vote
1
answer
22
views
How to model data which sometimes (but not always) has a subsection?
I'm trying to help my wife create a schema for a project of hers, and I'm somewhat stumped by some of her data and how to model it.
She has a bunch of "standards" that go in to a database. ...
- 13
2
votes
1
answer
517
views
Should a counter column with frequent update be stored in a separate table?
I have a MySQL/MariaDB database where posts are stored. Each post has some statistical counters such as the number of times the post has been viewed for the current day, the total number of views, ...
- 33
1
vote
2
answers
4k
views
Create an index for fields within an array of composite type
My question is a follow-up to the question answered here:
https://stackoverflow.com/a/15041094/994263
Considering a table with an array column of a composite type, is it possible with PostgreSQL to ...
- 113
-1
votes
1
answer
22
views
Table normalization of given table
I've got a table and I'm supposed to figure out why it breaks with 1NF, 2NF and/or 3NF.
I know the differences between these three, but I'm bad at telling what attributes are primary keys and what is ...
- 11
2
votes
1
answer
156
views
How to enforce UNIQUE constraint for sixth normal form in PostgreSQL
I have a table person which I am planing to normalize to the sixth normal form.
The table has attributes username, email, and phone. The reason I want to go to the sixth normal form is keeping the ...
2
votes
0
answers
49
views
How to enforce NOT NULL for sixth normal form in PostgreSQL
I have a table person which I am planing to normalize to the sixth normal form.
The table has attributes username, email, and phone. The reason I want to go to the sixth normal form is keeping the ...
0
votes
1
answer
165
views
Does combining multiple tables into one table with more than 100 columns improve performance and overall design?
I have 10 tables without any type of foreign key relations between them. All the tables have the same primary key. In this case, "stock_id" is the primary key. All table columns describe a ...
-1
votes
1
answer
867
views
How to query likes and user details for posts?
I'm pretty new to the world of databases and SQL.
I'm trying to build a system for users to submit posts and like posts. I've been looking at a few similar questions on this site, reading about third ...
- 101
0
votes
1
answer
2k
views
Strong vs weak entity types on SQL
I have a 3NF normalized database design and was trying to understand the concept of strong vs weak entity types and my understanding is weak entity types are tables that don't have a primary key. ...
- 21
1
vote
1
answer
120
views
normalisation in DBMS
For the relation schema R(A, B, C, D, E) with the following FDs
FD1: {A,B,C} → {D,E}
FD2: {B,C,D} → {A,E}
FD3: {C} → {D}
I Decomposed R into a set of BCNF relations.
I got candidate keys as {A,B,C},...
- 21
-1
votes
1
answer
447
views
Normalisation from 2nf to bcnf
This is a question from a textbook
Consider the Relation R={A, B, C, D, E, F, G, H } and the set of functional dependencies.
A→C B→CG AD→EH C→DF A→H
What is the key for R? Decompose R into 2NF, 3NF ...
- 3
0
votes
1
answer
386
views
How to create a trigger that generate a random specific output to insert into a table in MariaDB?
I have an account table which is laid out in 3NF:
CREATE TABLE account (
account_id INT AUTO_INCREMENT,
customer_id INT NOT NULL,
account_type_id VARCHAR(15) NOT NULL,
is_active ...
- 13