3

My database design is currently at 3NF. The issue is foreign keys and in some cases compound keys.

Can you move compound keys and/or foreign keys to create other tables provided the attributes associated with the compound/foreign keys do not rely on the primary key?

I suspect the answer is yes due to this link:

Are Foreign Keys included in Third Normal Form?
Best Answer: Just because it's a foreign key doesn't mean it also can't be considered an attribute of the primary key. The fact that it's a foreign key to begin with implies it's defining a relationship with another table, and thus would not violate [...] 3NF.
-- TheMadProfessor
https://answers.yahoo.com/question/index?qid=20081117095121AAXWBbX#

This leads me to wonder whether my current normalization stage is 3NF.

philipxy
  • 14,867
  • 6
  • 39
  • 83
user1655231
  • 71
  • 1
  • 11

3 Answers3

4

Preamble

In pure relational database theory, there is nothing to stop you having composite primary keys (PKs), and you can have foreign keys (FKs) that reference them and those FKs are necessarily composite too. Some software has difficulty with composite keys, so you often find that people add an ID column which contains an automatically generated number, which is then designated as the PK of the table. Other tables can then have (simple) FKs that reference the (simple) ID column. One not uncommon mistake is to forget that the original composite PK is still a candidate key (CK), and its uniqueness should be enforced by the DBMS with a unique constraint on the table; it becomes an alternative key (AK).

Diversion

The system of CKs, AKs and PKs works like this:

  • Every CK is a set of (one or more) columns that is a unique identifier for the data in the rest of each row of data in a table.
  • One CK may be designated as the PK.
  • The other CKs become AKs.

Consider this table:

CREATE TABLE elements
(
    atomic_number   INTEGER NOT NULL PRIMARY KEY
                    CHECK (atomic_number > 0 AND atomic_number < 120),
    symbol          CHAR(3) NOT NULL UNIQUE,
    name            CHAR(20) NOT NULL UNIQUE,
    atomic_weight   DECIMAL(8,4) NOT NULL,
    period          SMALLINT NOT NULL
                    CHECK (period BETWEEN 1 AND 7),
    group           CHAR(2) NOT NULL
                    -- 'L' for Lanthanoids, 'A' for Actinoids
                    CHECK (group IN ('1', '2', 'L', 'A', '3', '4', '5', '6',
                                     '7', '8', '9', '10', '11', '12', '13',
                                     '14', '15', '16', '17', '18')),
    stable          CHAR(1) DEFAULT 'Y' NOT NULL
                    CHECK (stable IN ('Y', 'N'))
);

Each of atomic_number, symbol and name is a candidate key. For chemistry, the symbol is most convenient as the primary key; for physics, the atomic_number is most convenient. The tables related to isotopes etc reference the atomic_number column, but the tables related to chemical compounds reference the symbol column. The three CKs here are all simple; on the other hand, the isotopes table has a compound PK consisting of the atomic number of the element (the number of protons) and the number of neutrons.

Answer

Getting back to your question, your data may well be in 3NF, or more likely BCNF (which is formally stronger than 3NF).

You'd have to show us your table schemas and specify the constraints (functional dependencies, etc) that apply to the columns before we could assess your design. But there is nothing that you've described which, a priori, prevents it from being well normalized.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
  • Thank you that helped see comments on Jays answer below. In a nutshell tried both concepts moved keys as regarding their dependency. – user1655231 Sep 11 '12 at 18:53
  • During normalization, it is perfectly kosher to move the columns which constitute a foreign key from one table to another if the foreign key is not determined by the whole primary key on the original table (so it is OK to move FKs around). – Jonathan Leffler Sep 11 '12 at 19:44
3

I'm not sure that I understand your question. If you are asking, "Can you have a foreign key in a table without violating 3NF?" the answer is absolutely, positively yes. Nothing about any stage of normalization says that you should eliminate foreign keys. Indeed, it's pretty much impossible to normalize all but the most trivial data without using foreign keys.

** Update **

Okay, maybe now I understand your question, but then I think you've answered it for yourself. Yes, in a fully-normalized DB, you should not have non-key dependencies. If you have FKs that are not dependent on the PK, then they should be moved to another table.

To make a simple example, suppose you want to keep track of people, the city they live in, and the country that that city is in. So for your first draft, you make this structure: (Asterisk marks the PK.)

Person (person_id*, person_name, city_id, country_id)
City (city_id*, city_name)
Country (country_id*, country_name)

This is not normalized. A city is in the same country regardless of what resident of that city we are talking about. Paris is not in France when we are talking about Pierre but in Germany when we are talking about Francois. (If there are two cities with the same name, of course those are different cities and should have different records. I suppose a city could cross national boundaries, but for our purposes here let's assume that if it does, we consider it two cities that happen to touch. They would surely have different city governments, different postal systems, etc.) So we have a non-key dependency. country_id depends on city_id, not on person_id.

So to normalize this schema, we should move the country_id to a table where it is dependent solely on the PK. Presumably, the City table:

Person (person_id*, person_name, city_id)
City (city_id*, city_name, country_id)
Country (country_id*, country_name)
Jay
  • 26,876
  • 10
  • 61
  • 112
  • Hello, this is what i mean I have a table with a primary key and 3 foreign keys. As part of the 3NF process "Remove columns that are not fully dependent upon the primary key". As foreign key2 does not depend on the primary key it depends on foreign key1 so can I move a foreign key 1 and create a copy that key that will remain in the table with the primary key and then move foreign key 2 to that new table with foreign key 1 that acts a the primary key of the newly created table with foreign key 2 that depends on Foreign Key 1 and not the primary key itself. – user1655231 Sep 07 '12 at 18:28
  • @user1655231: you need to outline your table structure so we can have some idea what you're talking about. You can edit your question and type in a CREATE TABLE statement that gives the key information, or you can simply list the table name and the column names, and identify the foreign key references. You may need to specify the functional dependencies too. – Jonathan Leffler Sep 07 '12 at 18:43
  • i was going to add the tables as pictures so you can get the concept quicker then list of the 1 to many and 1 to 1 relationships but the 10 points requiremnets stop me. I will write them out after looking at how others have been written out on this site and come back with that format – user1655231 Sep 07 '12 at 19:36
  • "If you have FKs that are not dependent on the PK, then they should be moved to another table." Thank you ! Tried both avenues of my query. (1) Normalize without moving and (2) Normalize with moving and decided on the moving them model and as no one who answered said "No you cannot move foreign keys". Therefore thought "you can" and did. I did then encounter another problem to which i posted an not to good question so hmm stuck for now will see if i can solve myself. – user1655231 Sep 11 '12 at 18:46
  • The idea that "I can't move this field" just isn't valid. There's nothing magic about the first place you happenned to put it. If you put it in the wrong table, you should move it. The usual way to teach normalization is to talk about creating new tables and moving fields between tables until all conditions that violate the rules of normalization have been corrected. – Jay Sep 12 '12 at 19:31
  • @user1655231 & Jay Normalization uses FDs & JDs to replace a table by projections of it that join back to it. FKs are *irrelevant*. They are constraints. When subrow values for a column list must appear elsewhere, declare a FK. Because components share columns, some FKs will arise from normalization. That is output not input. This answer is confused. See JonathanLeffler's answer. PS "Compound" applies to FKs, PKs, CKs (candidate keys), superkeys & is relevant to FD parts. The number of columns in PKs, CKs, superkeys & FD parts has nothing per se to do with normalization either. – philipxy Jul 13 '17 at 05:31
  • @philipxy If you mean that the fact that a database engine uses the "foreign key" keyword to signal that it should enforce an existence constraint, sure, this is irrelevant to the design of a data model. I am using the term FK here in the conceptual sense, to mean that this field is a posted identifier from another table. – Jay Jul 13 '17 at 16:29
  • @philipxy I'm not sure what you're getting at with your comment about compound keys. Sure, an FK or a PK can be compound. Not sure how that relates to the present discussion. Did anyone say or imply that it can't be? – Jay Jul 13 '17 at 16:30
  • Re "compound" The question in the question is "Can you move compound keys and/or foreign keys to create other tables provided the attributes associated with the compound keys/foreign keys do not rely on the primary key?" I should have said, similarly, number of attributes in keys is irrelevant to most normalization definitions & algorithms. Re FKs I mean "the conceptual sense". They are not used to normalize, although one can reasonably say some are output. See my answer expanding my comment. – philipxy Jul 13 '17 at 21:43
  • @philipxy RE compound: Oh, duh. Yes, it was in the question. Didn't really seem relevant when he got to the substance of his question so I forgot about it. :-) – Jay Jul 14 '17 at 16:38
  • @philipxy RE FKs irrelevant: I think you're quibbling over terminology. Whether you want to call it a "functional dependency" or a "posted identifier" or a "foreign key", it's the same concept. Yes, in SQL database engines "foreign key" is a keyword that has a specific, technical function. But that doesn't take away from the general meaning of the word. SQL database engines use the word "table" as a keyword with a specific technical implementation, but that doesn't mean that I can't or don't use the word "table" in the more general sense to describe a data model. – Jay Jul 14 '17 at 16:43
  • Re "quibbling" FDs & FKs are *two different things*. Maybe also the question is confused re FDs & FKs. But the title says "to another table" and the quote says "with another table" and FDs cannot be to another table while FKs can. Still, maybe confusion. But then an answer should point out that *FDs are not FKs* and other tables are irrelevant when normalizing. (I *already made clear* that I use "FK" in the relational not SQL sense. But anyway SQL use is based on relational use. Except that an SQL "FK" declaration actually declares a foreign *superkey*.) See my edited answer. – philipxy Jul 14 '17 at 22:04
0

Do you understand what a FD (functional dependency) is? A FD is an expression with an arrow between two attribute sets. Given a table and a FD, saying that the FD holds in the table or the table has the FD or the table satisfies the FD says all subrows for the first attribute set appear with the same subrow for the second attribute set.

Do you understand that normalization involves CKs (candidate keys)? Independent of normalization we can call one CK a PK (primary key) and the others AKs (alternate keys).

Do you understand what a FK (foreign key) is? Given a database, a table and an attribute list, saying that the list is a FK referencing some attribute list in some table says that every list of values for the attributes in the first table is also a list of of values for the attributes in the second table where those attributes form a CK (candidate key).

Normalization uses FDs & JDs (join dependencies) to replace a table by projections of it that join back to it. FKs (foreign keys) are irrelevant to normalization. That's both to whether a table is in a given NF (normal form) and to decomposing to a given NF. The answer you link to is also saying that FKs are irrelevant to whether a table is in 3NF--first for a specific case, then for the general case.

Because components share column values from an original table, some FKs will arise from normalization. Just as various tables and their PKs, AKs, CKs, superkeys, FDs & JDs will. That is normalization output, not input.

Since normalization replaces tables by projections of them, column sets in common among the original & components must contain the same subrow values. That's an EQD (equality dependency). Subrow values that form CKs will thus have FKs to them arise from normalization.

But often during normalization we see that we want to replace a table by some that are projections with others that have at least the subrows of a projection. Common subrows in a projection must then appear in expanded components, but not vice versa. That's an IND (inclusion dependency). There will still be a FK when common columns form a CK in a table that is a superset of a projection. Such a design change isn't normalization, it is just a change that you noticed during normalization from a design that was wrong in not allowing all the possible business situation to be recorded to a design that doesn't have that problem.

"Compound" vs "simple" vs "empty" apply to FKs, PKs, AKs, CKs (candidate keys), & superkeys and are relevant to FD & JD parts. Definitions will specify compound/simple/empty when it matters. (Eg: Sometimes FDs are put into canonical forms involving single columns. Sometimes we can easily infer NFs hold partly based on whether CKs are simple.)

After you get your tables, declare (sufficient) constraints. Then the DBMS can enforce them. FKs, PKs, AKs, CKs, superkeys, FDs & JDs all have associated constraints. SQL lets you declare all PKs & AKs (via PRIMARY KEY & UNIQUE NOT NULL). Those declarations actually declare superkeys which happen to be CKs/PKs/AKs when no smaller ones are declared within them. Similarly SQL FOREIGN KEY declares a foreign superkey that is a FK if it is actually to a CK. Declare sufficient chains of FKs to enforce the ones you don't declare. (Via transitivity.) SQL DBMSs typically won't let you declare FK cycles. SQL also makes you declare superkeys referenced by FK declarations whether or not those columns contain a smaller declared superkey/CK and so must be a superkey. Declare or enforce via triggers any FD or JD constraints that aren't implied by CK constraints. (5NF gets rid of all such constraints except some cycles of FDs on CKs.)

Find academic textbook definitions and algorithms.

philipxy
  • 14,867
  • 6
  • 39
  • 83