New SQL Server 2008 R2 OLTP Design

Question

New SQL Server 2008 R2 OLTP Design

SQL_ME_RICH

SSChampion

Points: 11200
More actions
June 17, 2011 at 1:43 pm

#253156

Having fun, but learning a lot of things the hard way. I started off attempting to get this into 3NF, only to find myself de-normalizing almost from the get go. So my question is (so I don't wind up with another STAR schema for an OLTP system - I'm still being laughed at for that one) is do each of the tables in my database HAVE to somehow be connected by some form of Referential Integrity? Or is it okay if I have groups of tables that all relate, but not necessarily to the others tables in the database schema?
I have 1 schema (dbo), and here is the design. I know this might be hard to visualize without understanding how it's going to flow with the front end Web app, but that's the same boat I am in right now as well (we are going to shelve this and maybe pick it back up again in like - 24 months? So I many not even be the guy who works on it at that point). Attachment included.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQL_ME_RICH SSChampion Points: 11200 More actions · Answer 1

P.S. I don't know how I did it, but a major relationship is missing from the snapshot I included. The Course tbl PKey has a relationship to the Forum tbl via the CourseID column. The design actually looks like this. . .

Source-NH SSCarpal Tunnel Points: 4165 More actions · Answer 2

Correct me if I am misreading this, but it appears that you have 3 distinct "clusters" of tables along with several stragglers.

1 - Courses / Events

2 - Locations / Participants

3 - Membership

From what I am seeing, the design that you have posted leaves the possibility of multiple registrations for the same Participant / Course combination (no links between Registrations / Membership or Registrations / Courses). This would mean that you would need to have either the front end or DB logic validate no duplicate registrations. Also, nothing tying the Courses to Locations. Not knowing your application, not sure if that is accurate or not.

Bottom line is that referential integrity is there to help you enforce the business rules in your design with a minimum of coding. Many people use the rule of thumb "normalize till it hurts, denormalize till it works", and that is a very appropriate comment. You should always balance the amount of work (coding and data maintenance) that it takes to achieve 3NF versus the performance / responsiveness of denormalized.

Short answer long, there is NO "correct" answer to your question. Use common sense to achieve the balance that you need for your particular situation.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

Hi Rich --

Looks like you put a lot of work on it - congrats.

Two notes.

1- Only dependent tables are "linked" by referential integrity

As an example, since it doesn't makes sense to have an InvoiceLine row with no parent row in InvoiceHeader table we usually define a 1:n relationship in between InvoiceHeader.InvoiceNbr (PK) and InvoiceLine.InvoiceNbr (FK)

2- We really do not want to see the front end Web app to understand the data model but the ER Model - Entity/Relationship Model. Do you have one?

In fact, ER Model most likely shows basic RI.

_____________________________________
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.

SQL_ME_RICH SSChampion Points: 11200 More actions · Answer 4

Paul / Source - thank you both very much for this input. Yes - there were some other relationships there that should have been added. There are still some straglers, and I am not certain that I want to tie together the aspnetdb tbls together with the main db tbls or not (this is coming from an older Cold Fusion app I should mention), and my apologies for lack of a true ERD (I do not have a tool to work with, and thus it's SSMS and Microsoft Paint to get my design together, and a whole heck of a lot of documenting).

SQL_ME_RICH SSChampion Points: 11200 More actions · Answer 5

Alright - I think I finally have it. There are only 2 lone tbls (from the aspnetdb tbls, and if that is how Microsoft wants them, so be it). I was able to tie together the last of the remaining tables to the Message tbl (ChatMessages et al, and the Reports tbl). I created a Unique Key for the UserID column on the Message table, and tied everyone else together that way into the main tbl design. Here is the result, and thank you all again for all of your input and feedback.