October 11, 2007 at 9:59 am
Using the same class doesn't in itself negate using multiple tables (that's what inheritance is for). I prefer the separate tables approach, and using the sub-classes/inheritance in the app code, because it gives me the best of both worlds: reusable code for the object (the only thing you'd need to override is how you populate/instantiate it), but actual data integrity/relational integrity enforced at the data layer (in addition to the app layer). App-layer relational integrity is too easy to override, or forget in my mind.
As I mentioned at the very beginning - I don't think it's a deal-breaker one way or the other.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 11, 2007 at 10:03 am
Ahhh, glad my brain works a little bit out of SQL anyway.
As far as doing something wrong, nope you are not. The problem with the 2 table design is that you could get a status for another entity in the Employee table. You need to write code to prevent this from happening.
Now, I typically would never grant table access to any users, but create stored procedures that users have execute rights on. Then within the the stored procedures I enforce RI in these situations. I always make the FK relationships in the DB. With this process only SA's or users with DBO rights (which would only be SA's) in the DB can directly change the data in the tables. You can also use triggers to ensure RI in these scenarios. Triggers, although they can be bypassed by someone who knows what they are doing, will enforce RI regardless of how you alter the data.
99% of the time I try to avoid enforcing RI in code and the DB Engine do it, that's what it is there for, but sometimes you may need to do it yourself. As one regular forum poster puts it (can't remeber the name), "As always, the answer is it depends".
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 11, 2007 at 11:00 am
Thanks for the advice. I was not 100% sure that I had a handle on this. Unfortunately in my company, I am the System architect, Lead Programmer and DBA. While I enjoy this diversity, being the jack of all trades and the master of none makes me question myself.
Not too belabor a point, but I just want to be sure of a few things:
1. I should add a FK Constraint on my Employees table to my StatusSub table (I realize that this won't guarantee RI, but it could be of help)?
2. My Employees table does not need a LookupType field that maps to the Lookup Type field in my StatusMain table?
3. My Employees table does need a Value field that maps to the Value field in my StatusSub table?
Thanks. I really appreciate the help.
October 11, 2007 at 11:09 am
Thanks. I am not using inheritance for my lookup tables. I use a static class that has 2 methods.
GetByPrimaryKey(string lookupType, objectKey)
GetAll(string lookupType)
The first method returns what I call a LookupRow and the second method returns a collection of LookupRows.
Is there a better way to do this?
Thanks
ps. my manager wants Inserts/Updates and Deletes to be done by me via SQL.
October 11, 2007 at 12:26 pm
1. I should add a FK Constraint on my Employees table to my StatusSub table (I realize that this won't guarantee RI, but it could be of help)?
Correct. This will guarantee that you at least have a vaild Status in the table. Even if it is one for customer instead of employee.
2. My Employees table does not need a LookupType field that maps to the Lookup Type field in my StatusMain table?
Correct.
3. My Employees table does need a Value field that maps to the Value field in my StatusSub table?
I'm not sure exactly what you mean by a the value field. If you mean 'New' or something like that then
no. In these cases I would have a surragate key I use as a non-clustered primary key for FK relationships (usually an integer identity field) called Id so my tables would be like this:
StatusMain(StatusMainId Int Identity(1,1) Primary Key, StatusTypeDesc Varchar(15))
StatusSub (StatusSubId Int Identity(1,1), PrimaryKey, StatusAminId Int, Desc varchar(15))
Foreign Key on StatusMainId to StatusMain
Employee(Name, etc.,, StatusSubId)
Foreign Key on StatusSubId To StatusSub
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 12, 2007 at 7:27 am
Your design is different then I expected (I'd say better). In my design I had two tables that had the following fields:
StatusMain - StatusMainID (Identity), TypeCode, Description
StatusSub - StatusSubID (Identity), TypeCode, FieldValue, Description
Using this design, I put the FieldValue in my Employees Table.
Your design looks to be cleaner then mine. I would appreciate it if you could clarify two things for me.
StatusMain(StatusMainId Int Identity(1,1) Primary Key, StatusTypeDesc Varchar(15))
StatusSub (StatusSubId Int Identity(1,1), PrimaryKey, StatusAminId Int, Desc varchar(15))
I am not 100% sure of the relationship between StatusMain and StatusSub. Is StatusAminId a foreign key that points to StatusMainId? If not, how does StatusMain relate to StatusSub?
Am I correct that by putting the StatusSubId in the Employees table that I have eliminated all RI problems or is there something I need to test?
Thanks. I really appreciate the time and advice you have given me.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply