January 13, 2010 at 7:57 am
I am looking at designing a new table structure to store the data captured from a specific form. To keep table sizes manageable a table is being created per section (otherwise I would have a single table with many hundreds of fields, many of which should be indexed). The catch is that I want to have everything related by a single key.
Header:
docID*
<fields>
SectionI:
secIID*
docID (FK->Header.docID)
<fields>
Up to this point it is nothing special. The catch comes when Section I has within it repeating data...what I would like to do is something like this:
SectionIOffices:
officeID*
docID (FK->SectionI.docID)
<fields>
This maintains the single id across my tables and the logic of the document. I guess what I am looking for are potential problems from having a foreign key reference to a foreign key. Each document has exactly 1 SectionI.
Thanks.
January 13, 2010 at 8:10 am
remember a foreign key can be created only on a column with a primary key or unique constriant; on it, an integer column that happens to have an FK doesn't qualify for a FK(yet), so
so to do what you are thinking, you'd havew to put a unique constraint on SectionI.docID .
if it were me, i'd just point the FK to the original header table, but you can do it as you described.
Lowell
January 19, 2010 at 8:16 am
Two thoughts...
1) A design that ends up with two or more tables having a 1-1 relationship most of the time means something is wrong.
2) No physical design should include n-n relationships; if the domain model shows a n-n relationship an additional table should be created to split the desired n-n relationship into two 1-n relationships.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 20, 2010 at 5:49 am
PaulB-TheOneAndOnly (1/19/2010)
1) A design that ends up with two or more tables having a 1-1 relationship most of the time means something is wrong.
Why?
It is true that such relationships between tables can't usually be enforced with foreign keys because one side of the relationship always has to be optional when new rows are inserted. Usually the best compromise possible in SQL is to make constraints deferrable or make one side permanently optional. That's a limitation of SQL however, it doesn't mean anything is "wrong" with the design.
Assuming that for practical reasons 1->1 actually means 1->{0/1} then that kind of relationship makes perfect sense sometimes. For example: "Each Department has exactly one Manager", "Each Payment Authorisation has zero or one Payments".
January 20, 2010 at 6:09 am
David Portas (1/20/2010)
PaulB-TheOneAndOnly (1/19/2010)
1) A design that ends up with two or more tables having a 1-1 relationship most of the time means something is wrong.Why?
It is true that such relationships between tables can't usually be enforced with foreign keys because one side of the relationship always has to be optional when new rows are inserted. Usually the best compromise possible in SQL is to make constraints deferrable or make one side permanently optional. That's a limitation of SQL however, it doesn't mean anything is "wrong" with the design.
Assuming that for practical reasons 1->1 actually means 1->{0/1} then that kind of relationship makes perfect sense sometimes. For example: "Each Department has exactly one Manager", "Each Payment Authorisation has zero or one Payments".
Sorry but you got it all wrong mate 😀
When I say 1-1 relationship I'm not saying 1-0/1; I'm saying 1-1 and I insist that a 1-1 relationship most certainly means something is deadly wrong.
Despite a couple of well supported examples where my statement does not hold true the best thing you can do when facing a 1-1 relationship is to merge those two tables into a single one. On the long run a single table rather than two tables with a 1-1 relationship will decrease overhead during quering, will provide a much easier to understand platform for developers and will be easier to maintain.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 20, 2010 at 6:22 am
PaulB-TheOneAndOnly (1/20/2010)
When I say 1-1 relationship I'm not saying 1-0/1; I'm saying 1-1 and I insist that a 1-1 relationship most certainly means something is deadly wrong.
What is "wrong" with the concept of having only one Manager per Department and one Department per Manager? Are you really saying you would recommend putting all attributes of managers and departments into the same table? To my mind that seems pretty ugly and unfriendly to people who have to use the model. It also has the disadvantage that if a manager moves department then you have to move all his data instead of just change the key attribute(s). I doubt that's going to be more efficient than having a table for each.
January 20, 2010 at 6:34 am
David Portas (1/20/2010)
PaulB-TheOneAndOnly (1/20/2010)
When I say 1-1 relationship I'm not saying 1-0/1; I'm saying 1-1 and I insist that a 1-1 relationship most certainly means something is deadly wrong.What is "wrong" with the concept of having only one Manager per Department and one Department per Manager? Are you really saying you would recommend putting all attributes of managers and departments into the same table? To my mind that seems pretty ugly and unfriendly to people who have to use the model. It also has the disadvantage that if a manager moves department then you have to move all his data instead of just change the key attribute(s). I doubt that's going to be more efficient than having a table for each.
Please read what I write - not what you think I'm writing 😉
It is obvious that PERSONNEL (which includes managers - actually an attribute of PERSONNEL) and DEPARTMENTS are two different entities that must be modeled then physically implemented as two different tables. By the way relationship should be like PERSONNEL <<---> DEPARTMENTS, init? 😎
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 20, 2010 at 6:47 am
PaulB-TheOneAndOnly (1/20/2010)
It is obvious that PERSONNEL (which includes managers - actually an attribute of PERSONNEL) and DEPARTMENTS are two different entities that must be modeled then physically implemented as two different tables. By the way relationship should be like PERSONNEL <<---> DEPARTMENTS, init? 😎
That's a different example however. If you don't have a Managers table then how would you enforce the basic rule that only managers can manage departments? What about attributes and constraints that only apply to managers and attrbutes and constraints that only apply to non-managers? Are you going to make every attribute nullable just so that you can bundle two different sets of attributes into one table?
Going back to my original example however. Suppose there is no Personnel table at all. We are only required to record information about managers and departments. If Managers and Departments are "two different entities" then isn't it sensible to give them two different tables?
January 20, 2010 at 7:00 am
oh boy 😀
1- the statement that reads "only managers can manage a department" does not holds true in the real world. In the real world you are going to find departments directly managed by "directors", departments managed by and "acting-manager" that remains an "associate", etc.
2- the statement that reads "there is a 1-1 relationship between managers and departments" does not holds true in the real world. In the real world you are going to find "managers" that manage more than one "department" and even departments with no manager.
Here the good news! as I said before it is not only the sensible thing to do but also a must-do to model different entities as different tables. As you can see we are in agreement here! Cheers! 😎
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 20, 2010 at 7:15 am
So your only answer to my question is to tell me that I'm not asking the right question. Right...
You can invent an exception to any business rule you care to mention if you wish. The same arguments could be used against any kind of constraint in any database - that one day it might not apply in the real world. The point of such constraints is that they raise an exception when a business rule determined by a busines stakeholder is about to be broken.
In fact 1-1 constraints are not so special. They are just one example of a 1-N constraint where N>0. Such constraints are extermely common in business requirements. One example being a rule that an Order must contain at least one Product - a rule which is not enforcible by a foreign key alone because foreign keys are always optional on one side of the constraint.
January 20, 2010 at 7:39 am
David Portas (1/20/2010)
So your only answer to my question is to tell me that I'm not asking the right question. Right...
No David, it's not the questions, it is the supporting scenario that is wrong... just can't defend a physical model 1-1 relationship in an scenario that calls for a 0/n-0/1 at the domain model level, init?
I feel you are looking to find the specific-especial-narrow case where your theory is correct but lets forget about it.
Since the burden of communication rests on the one trying to convey the message it is clear to me that this is my fault so, lets apply Rule #92 and allow me to start again...
- Hereby I recognize the existance of 1-1 relationships so help me Codd.
- Hereby I insist most of the time a 1-1 relationship is telling the modeler that he/she is bound to implement a single entity as two different tables - which most of the time is the wrong thing to do.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 20, 2010 at 7:53 am
PaulB-TheOneAndOnly (1/20/2010)
just can't defend a physical model 1-1 relationship in an scenario that calls for a 0/n-0/1 at the domain model level, init?
Of course I agree. However in a scenario where the domain model does call for a 1-1 relationship then you cannot automatically assume that the "best" implementation is a single table. A table should represent a unique entity type which is identifiable by its unique set of attributes. It does not follow that representing a 1-1 relationship with two tables is always or mostly wrong because that depends on other factors entirely.
January 20, 2010 at 8:10 am
David Portas (1/20/2010)Of course I agree. However in a scenario where the domain model does call for a 1-1 relationship then you cannot automatically assume that the "best" implementation is a single table.
I'm glad we are in agreement, especially because I do not automatically assume that the "best" implementation is a single table... let me quote myself when I said in my first post that "A design that ends up with two or more tables having a 1-1 relationship most of the time means something is wrong".
Subtle but important difference, init? 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 20, 2010 at 8:23 am
I would love to have a single table, and it was strongly considered, but the purpose of this database is reporting, and people are rarely interested in the entire document. People tend to want data from certain areas, and within those areas we have certain significant fields that will be used as criteria.
In trying to figure out how to handle this, we were unable to find much in the way of guidelines on the number of columns at which such a table should be broken into sub tables but there were concerns of maxing out the amount of data per row.
At what point are there performance concerns with really large tables (# of columns) and a good many indexes (to avoid scans on popular criteria fields)?
January 20, 2010 at 9:32 am
dbarr (1/20/2010)
At what point are there performance concerns with really large tables (# of columns) and a good many indexes (to avoid scans on popular criteria fields)?
It all depends on indexing strategy, table utilization and query tunning.
For tables you expect to get over 100 million rows I strongly suggest to entertain the idea of partitioning but please remember partitioning strategy has to be good helping either quering and/or purging processes - a bad partitioning strategy is DBAs worst nightmare.
I've seen two-billion rows tables performing pretty well - good partitioning and indexing strategies as well as carefully fine tuned queries - off course 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply