24
Normalization absolutely is used in the real world... and hopefully you know that 3NF is only the third one of... what is is now, 8? But 3NF should be an easy target.
However... I would venture to say that there could not be such a tool.
Normalization, technically, is an attribute of each table. Within a given database, different tables may have ...
20
The problem you face is known as "Normal forms" of databases, especially the first normal form. https://en.wikipedia.org/wiki/First_normal_form.
Your databse with the concatenated user IDs (first version) is not in first normal form.
See https://en.wikipedia.org/wiki/Database_normalization for why and how normalisation is generally considered good.
In ...
19
If it fits within the rules of normalization, then 1:1 relationships can be normalized (by definition!) - In other words, there is nothing about 1:1 relationships that make it impossible for them to obey the normal forms.
To answer your question about the practicality of 1:1 relationships, there are times when this is a perfectly useful construct, such as ...
19
Another way (without Nulls and without cycles in the FOREIGN KEY relationships) is to have a third table to store the "favourite children". In most DBMS, you'll need an additional UNIQUE constraint on TableB.
@Aaron was faster to identify that the naming convention above is rather cumbersome and can lead to errors. It's usually better (and will keep you ...
19
By changing to your proposed solution you lose information from the database. The existing solution says what clubs can exist in a particular school irrespective of anyone actually being in that club at any point in time. The proposed solution requires someone to join the club before the club comes into existence (i.e. before a row is written to the database)...
18
Managing an individual piece of information
Assuming that, in your business domain,
a User can have zero-one-or-many Friends;
a Friend must first be registered as a User; and
you will search for, and/or add, and/or remove, and/or modify, single values of a Friend List;
then each specific datum gathered in the Friendlist_IDs multivalued column represents a ...
18
Yes, the identification of many-to-many (M:N for brevity) associations or relationships is a situation that a database practitioner faces quite commonly when laying out a conceptual schema. Associations of said cardinality ratios come about in business environments of very different nature, and when properly represented at the logical level by means of, e.g.,...
17
One reason for normalisation is to remove data modification anomalies
ORMs usually do not support this.
I have many examples of Hibernate-designed databases that break this principle:
bloated (string repeated over 100s of millions of rows)
no lookup tables (see above)
no DRI (constraints, keys)
varchar clustered indexes
unnecessary link tables (eg ...
17
I spent 7 years developing software for a publishing company and one of the hardest problems we ever tackled was parsing street addresses in subscription lists. It is useful to split up addresses into distinct fields, but you can never, EVER design for every possible pathological aberration of address formats and components the human brain can devise.
...
16
I'm very familiar with cryptocurrency and databases, and I can tell you it's not a great DB engine at all.
Using the blockchain as a live database:
Think of it as a first normalized form without any really good built in search capability or indexing as far as the blockchain goes. Basically a excel sheet without any computation capabilities that just ...
15
You have pretty much 4 choices:
NoSQL - definition Every record is stored as a set of Key/Value pairs. It is very flexible and fast. Not all of the report writers out there support this style of storage. There are many example database implementations of NoSQL. The one that seems to be most popular right now, is MongoDB.
EAV - definition This is where you ...
15
Is blockchain a potentially viable database solution for modern, high transaction volume applications?
The blockchain technology in general has some characteristics that make it difficult to work with high volumes.
Take a look at Bitcoin for example. The average transactions per day have never been more than 300K: Transactions per day (source blockchain....
15
If a relation does not have any candidate key (and the primary key is just one of the candidate keys), then it can have duplicate rows, so in fact it is not a relation! (since relations are always sets).
In that case, it is more correct to call it a table, not a relation, as you did in your question, and note that several RDBMS in effect can manage not-...
13
There's a little aphorism that goes:
The key (1NF)
The whole key (2NF)
And nothing but the key (3NF)
. . . So help me Codd
In your example we can assume 1NF to begin with as the relational structure doesn't imply any repeating groups within the row (i.e. no D1, D2, D3 etc.).
R = {a, b, c, d, e, f, g} F = {AB --> C, A --> DE, B --> F, F --> GH,
D --> ...
answered Apr 9 '12 at 17:23
ConcernedOfTunbridgeWells
16.3k2 gold badges50 silver badges69 bronze badges
13
it seems they prefer having a surrogate key for every table even if some have primary keys like 'email' - breaking 2NF outright.
Surrogate keys don't break 2NF. 2NF says "If a column is dependant on only part of a multi-valued key, remove that column to a separate table."
They stipulate that it's just better to have everything as one big table no matter ...
13
Proper solution
The core of the problem is the data model. In a normalized schema, you wouldn't store name and email redundantly. Could look like this:
CREATE TABLE name (
name_id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL,
verified BOOLEAN NOT NULL DEFAULT FALSE,
UNIQUE (name, email)
);
...
12
The more things change, the more they stay the same. There have always been lazy developers who cut corners or just don't know or want to follow best practices. A lot of the time they can get away with it in smaller applications.
It used to be jamming COBOL-inspired data structures into early RDBMS, or the God-awful mess that was dBase. Now it's ORMs and ...
12
It seems that you are involved in a project that requires the creation of a temporal database. You may as well find related information by searching for the terms auditable databases and database history tables.
I deem this exceptional Stack Overflow answer as the top material with respect to these topics. In such a post, @PerformanceDBA models an auditable ...
12
If, as stated in your post, the intention is to create a relational database (RDB for brevity) and, therefore, it is expected that it functions as such, the short answer is:
No, you should not overlook data integrity constraints.
The primary objective should be to manage the pertinent data as it is, a quite valuable organizational asset, and a reliable ...
12
There is a difference between redundant data (bad) and coincidentally repeated data (not bad). Normalization is a technique which is used to avoid insert, update and delete anomalies. It is not meant to eliminate every repetition of a piece of data. Data which is static doesn't benefit from normalization.
A unit of measure, stated as a standardized ...
11
Arguably, it doesn't.
Adding a surrogate key is an implementation decision (to respect how the RDBMS works) taken at implementation time. During modelling and normalisation, you should end up with BCNF (slightly stricter and more correct 3NF) without surrogate keys
That is, introducing surrogate keys at the start of the design process is wrong. Even though ...
11
Without the full table definition, it is difficult to provide a perfect answer. However, in an attempt to show the differences in a limited repro, with a very small amount of data, I've created the following testbed:
IF OBJECT_ID('tempdb..#src') IS NOT NULL
DROP TABLE #src;
CREATE TABLE #src
(
EmployeeCode INT NOT NULL
, WeekStartDate DATE NOT NULL
...
10
There are few things to consider other than normalization. For instance, you have a column for AGE. Are you going to update that every year? How will you know when to do that? The same goes for years of experience.
There are some columns that will probably have multiple values for each applicant: School, Course, etc.
You may also want to check your ...
10
Business rules
Based on (a) some deliberations we had via comments and (b) the content of the question, we have defined the following characteristics of your business environment:
A Product is primarily identified by its Id
A Product is alternately identified by its Name
A Product is alternately identified by its SKU
A Product is offered at a Price
...
10
Problems that can be solved by splitting include
Validation Any one part of the name can be compared to a master list. Those which do not match can be rejected. Postcode / zipcode is an obvious example. These are issued and maintained by an independent authority. The only valid ones are those issued by that authority.
Sorting and Selection I have seen ...
9
I agree that memory constraints did bear a direct correlation to
normalization...
Memory constraints still matter. Quantity isn't a problem, speed is.
CPUs aren't getting any faster at the moment (We're getting more core's, not cycles per second)
Modern CPU architectures attempt to overcome the speed limitation by providing separate memory for each ...
9
It depends on what your priority is. Do you want to avoid work or do you want to adhere to the strictest rules of normalization?
Personally, I think it is better to have IsFavorite in the child table, and would be willing to put in the work to make sure that at most one child for every parent is that parent's favorite. The primary reason has nothing to do ...
9
This sounds like a really simply one-to-many relationship.
For SQL Server, I would write this like:
CREATE TABLE Devices
(
DeviceID INT
, DeviceName nvarchar(255)
);
CREATE TABLE Cards
(
CardID INT
, CardName nvarchar(255)
, DeviceID INT
);
CREATE TABLE Ports
(
PortID INT
, PortName nvarchar(255)
, CardID INT
);
INSERT ...
9
You're right on the money with the possible candidate keys, vikkyhacks. Overlapping candidate keys are composite (consist of more than one attribute) candidate keys with at least one attribute in common. So your overlapping candidate keys are NM and NO (they share N).
Additional explanation of the above, originally left in comments:
All overlapping ...
9
Your relation is in 3NF, (and not only in 2NF), since as you say the only non prime attribute is Grade, which only appears on the right hand side of your FDs.
The relation is not in BCNF, because the left hand side of the two small FDs is not a superkey.
You can, however, losslessly decompose the relation to
(SubjectCode, SubjectName) and either
(...
Only top voted, non community-wiki answers of a minimum length are eligible
