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.
670
questions
0
votes
0
answers
39
views
Database model for multi-channel notification system
We're currently redesigning the way our application sends out notifications to users (so far, it has been mainly calls at various places in the code, directly sending mails to the user) to a system ...
- 101
-1
votes
1
answer
57
views
Customer Address Design Probelm
I'm designing a database for E-commercial. So the Customer have many address also 1 address also has many customers owned so I have another CustomerAddress table (many-to-many relation). Here detail :
...
- 3
-1
votes
1
answer
125
views
Create a category column or a table for each category?
Which is better?
Table: Words
Columns: ID, Word, ParentWordID, CatID
Table: Categories
Columns: CatID, Category
Or:
Table: Words
Columns: ID, Word
Table: Categories
Columns: CatID, Categories
...
- 99
-1
votes
1
answer
54
views
Should we repeat multilevel dependency reference columns to avoid multiple joins breaking the normal forms for better performance
In general I know repeating information in tables are not considered a good database design
But I have a question for following particular design for multi level parent child relationship
...
- 119
0
votes
2
answers
82
views
3NF normalized table design
I am struggling with how to normalize a database where there is multiple addresses pr. user.
The situation is that a user can have both a regular address and a delivery address. When a user make an ...
- 111
0
votes
1
answer
54
views
Why do these examples not in 5NF exhibit update problems?
The tables below (Fig 17.20) were extracted from the book 'An Introduction to Database Systems' by C. S. Date, v. 1 -Longman Higher Education (1985) 4th edition.
I don't understand the need for ...
0
votes
2
answers
99
views
Handling two different types of relationships between same two tables
I'm working on a database where I have two tables where there can be different types of relationships between them, and I'm struggling with the best way to handle it because all the options I seem to ...
- 121
1
vote
1
answer
48
views
Why is the following decomposition into 2NF wrong?
Consider the relation R(A, B, C, D, E), with the functional dependencies being {BE->A, D->E}. What is R's decomposition in 2NF?
The answer is R1(D, E); R2(B, E, A); R3(B, C, D)
I've derived that ...
watson
0
votes
1
answer
105
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
0
votes
0
answers
45
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 ...
- 129
1
vote
2
answers
71
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
);
...
- 21
-1
votes
1
answer
15
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
2
votes
1
answer
134
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 ...
- 31
-1
votes
1
answer
36
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
27
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,693
0
votes
1
answer
79
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
43
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
37
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
97
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
74
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
1
answer
81
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
138
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
85
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
618
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
85
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
101
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
139
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
47
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
73
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
1k
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
63
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
82
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
226
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
53
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
1k
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
38
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
86
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
61
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
2k
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] |...
- 533
1
vote
1
answer
155
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
128
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
715
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
190
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
224
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
28
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
801
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
5k
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