Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sth_Weird
Starting Member
38 Posts |
 Posted - 2014-09-29 : 05:25:45
|
| another question (SQL Server 2008)...I store some kind of hierarchy/tree in my database. There are four levels and I have to store different attributes for each level, thus I have four tables.Let's call them Level1, Level2, Level3, Level4, with Level1 being the top level and so on. There are 1:n relationships between the tables (tree hierarchy). Each table has an ID and a foreign key ID that points to its parent in the hierarchy (Level 4 points to Level3, Level3 points to Level2, Level2 points to Level1). Now, Level1 (the top level) has another foreign ID that points to an entry (primary key) in another table, let's call that table Context. The question is, do I have to hand down the ContextID to all the other Level_X tables or is it sufficient if I only store it once in the Level1 table. My first guess was that it's sufficient to only store it in the Level1 table because the other Levels are somehow related to that table and thus inherit that information somehow (I can get the information with queries). The thing is, in Level4 (the lowest level in hierarchy), I want to store a foreign key that points to a primary key in another table, let's call that table DataDetails. DataDetails once again points to the ContextID of the Context Table. And of course, I may only point to those entries in that table that match the ContextID of the Level1 table. How can I archieve that? I mean I can easily handle the restrictions in my front end programme, but (how) can I restrict this in the database itsself? I mean if I pass down the ContextID in all Level_X tables, do I have to define an index between the primary key and the ContextID and use those two fields for the relationships instead of just the Primary key? Is that correct? If not, what do I have to do? Or should I stick to using the ContextID only once and let my front end programme handle the restrictions? I hope you understood my problem. If not please ask :)Thankx in advancesth_Weird |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-09-29 : 09:55:39
|
| for the first question, "do I have to hand down the ContextID to all the other Level_X tables or is it sufficient if I only store it once in the Level1 table?" you don't have to have ContextID in all the other levels. You can join up, level-by-level to level one to get it. However, this will make longish joins every time you need the context id (could be captured in a view, though). If you find it too verbose or underperforming you may want to carry the ContextrId in the lower level tables as wellSecond question (about Level 4), this is a good argument for carrying the context id in the lower level tables. In fact, that's probably what I would do. You don't have to define a compound index with the PK and ContextId; it depends on the queries you write. FWIW I would not put the integrity checks in the front-end. For one thing, someone else may write another front end or modify yours and forget or mess up the constraint checking. What I would do is encode the database logic in a set of stored procedures for CRUD operations and deny direct access to the tables thereby forcing devs to use the procs for their work. Here's an article that discusses that approach: http://www.pawlowski.cz/2011/01/automating-crud-procedures-generation-t-sql/ |
 |
|
|
sth_Weird
Starting Member
38 Posts |
Posted - 2014-10-06 : 04:43:26
|
| Thank you for your answer! Can you please explain how exactly to hand down the column? I mean right now Level1_ID is the only primary key in the top-level Level1 table. Do I have to include ContextID in my primary key as well? Or define an index or unique key on those two columns? Which is the right way? What about the tables for the other levels? Doing it properly, if I hand down ContextID, do I also have to hand down the other primary keys of each table? Taking Level4 for example, I would end up with including ContextID, Level1ID, Level2ID, Level3ID (and Level4 ID as the "actuall" ID column) which is quite a lot, or is it sufficient to only hand down ContextID and Level3ID? BTW I don't want to define any stored proceedures since the database should be as independant from sql server as possible because that has changed in the past (we started using ms access) and may change in the future (maybe they come up with the idea to use oracle in some time).thxsth_Weird |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-10-06 : 09:47:14
|
| If your company is now committing to sql server, it is unlikely they will move off it any time soon, if ever. It gets too expensive. You'll need to redo your databases, queries and processes, retrain your people, maybe buy new hardware. Unfortunately, each database vendor implements SQL in its own way and usually extends it freely. Once you get an application up on one RDBMS it is usually a non-trivial operation (i.e. expensive) to translate it to another one. Not using stored procedures means tons of pass-through queries in your applications. This may be OK for querying but is simply unacceptable for INSERT, UPDATE and DELETE since there is no way to control how those queries are written nor to enforce business logic. Stored procedures can give you that.Back to your design, how much information you duplicate at various levels depends on how much you query it. The choices are:1. Fully normalized data ==> use joins to get related data2. partially-normalized ==> keep some common information in more than one table, for easy access. ENFORCE UPDATES through stored procedures! Otherwise there's no way to guarantee that developers will always propagate the data as required.3. Un-normalized data ==> keep everything everywhere (nightmare for INSERT, UPDATES and DELETES). The approach you take depends on the business rules you need to follow and the performance levels you need to hit. There's no one-size-fits-all design.Start with a 3rd-normal form, enforce it through foreign keys, aggressive check constraints and views and stored procedures. If performance problems arise that you can definitely trace to the many joins, and you have already implemented appropriate indexes, consider de-normalizing your data a bit. Then, test, test, test. Prove to yourself that your modifications make the situation better, not worse! |
|
|
|
|
|
|
|
|
how to pass on foreign ids