January 25, 2011 at 4:16 am
Hi all,
This is my first post here. I am designing a database. The database is going to be in SQL Server 2005 and to be used as a backend for a VB.NET 2005 project. I am not used to creating databases but have tried it. Can anyone here look over my ERD and give ideas for improvements.
I am going to be using CASCADE UPDATE and CASCADE DELETE on some of the tables but not sure which ones. My ERD is attached.
Thanks in advance for any input provided.
Unless otherwise stated, I use Microsoft Visual Studio 2005 Professional and Microsoft SQL Server 2005.
January 25, 2011 at 11:08 am
I'm not a fan of CASCADE. It takes away a lot of control and can lead to unintended consequences. I'd be careful about using it.
As far as the design goes, not knowing the business requirements that are driving it, it looks reasonable. A few things jump out. You're using a bit for IsDeleted and you have a date field for when it was deleted. I'd dump the bit & just go with the value in the deleted date column. That'll tell you everything you need right there.
What are the CanUpdate bits or whatever they were on a bunch of the tables? What are those fields meant to do?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 25, 2011 at 11:29 am
Some quickies in addition to Grant's questions, and I agree on Cascades. I hate them with a passion. It's almost worse then the hidden logic behind triggers.
Why is OperatingSystem self-joining?
Why do you allow nulls in ProtectionTools bits when you don't anywhere else?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 25, 2011 at 3:31 pm
Grant Fritchey (1/25/2011)
I'm not a fan of CASCADE. It takes away a lot of control and can lead to unintended consequences. I'd be careful about using it.
I was reading up about it and to my understanding is that if the parent is deleted, so is the child. Same for update. I am not a fan of it too because I do not want to design it wrong and then something unexpected happen.
A few things jump out. You're using a bit for IsDeleted and you have a date field for when it was deleted. I'd dump the bit & just go with the value in the deleted date column. That'll tell you everything you need right there.
The record in the WorkLog table is where the work log is,of course. I figured that the end-user could mark the record as deleted and when searching for records, the ones marked as deleted for the bit field wouldn't come up. If they search for deleted work logs, they could always undelete and then mark that bit field as false and get the work log back.
What are the CanUpdate bits or whatever they were on a bunch of the tables? What are those fields meant to do?
Many of those tables populate certain drop down menus on the work log screen in the vb.net app. i used those bit columns to allow or disallow deletion for certain options.
Unless otherwise stated, I use Microsoft Visual Studio 2005 Professional and Microsoft SQL Server 2005.
January 25, 2011 at 3:36 pm
Craig Farrell (1/25/2011)
Some quickies in addition to Grant's questions, and I agree on Cascades. I hate them with a passion. It's almost worse then the hidden logic behind triggers.
I have never used triggers before
Why is OperatingSystem self-joining?
Why do you allow nulls in ProtectionTools bits when you don't anywhere else?
I noticed that the other day after posting my thread and unchecked the allow nulls for the CanDelete and CanModify columns in the ProtectionTools table.
As a note, all the CanDelete and CanModify columns have been set up to have a default value of true for when the end-user adds additional options. IsUsed is defaulted to false. Pretty much,the IsUsed column is used to check for when someone wants to delete that particular entry/row. if an existing worklog record has used a particular column within the other tables, they can't delete the item because its been used.
I hope I am making sense.
Unless otherwise stated, I use Microsoft Visual Studio 2005 Professional and Microsoft SQL Server 2005.
January 25, 2011 at 3:44 pm
I was considering creating a new table called DeleteModify and then have a bit field within that table. Holding two records for true and false. Then creating a relationship to the other tables but thought that would be overkill.
Unless otherwise stated, I use Microsoft Visual Studio 2005 Professional and Microsoft SQL Server 2005.
January 25, 2011 at 3:56 pm
spmaguire (1/25/2011)
I was considering creating a new table called DeleteModify and then have a bit field within that table. Holding two records for true and false. Then creating a relationship to the other tables but thought that would be overkill.
Not just overkill, that design would hurt you.
I understand what you're doing with the bit field, but having the deleteddate is enough. If it's null, the field is not deleted. If it has a value, it is.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 25, 2011 at 4:09 pm
Grant Fritchey (1/25/2011)
spmaguire (1/25/2011)
I was considering creating a new table called DeleteModify and then have a bit field within that table. Holding two records for true and false. Then creating a relationship to the other tables but thought that would be overkill.Not just overkill, that design would hurt you.
I thought so too and is the reason why I didn't do it.
I understand what you're doing with the bit field, but having the deleteddate is enough. If it's null, the field is not deleted. If it has a value, it is.
That makes sense to me. I'll go ahead and remove that column and just check to see if a date exists or not. Thanks for the suggestion.
Unless otherwise stated, I use Microsoft Visual Studio 2005 Professional and Microsoft SQL Server 2005.
January 25, 2011 at 4:20 pm
spmaguire (1/25/2011)
Pretty much,the IsUsed column is used to check for when someone wants to delete that particular entry/row. if an existing worklog record has used a particular column within the other tables, they can't delete the item because its been used.
This sounds to me like the entire purpose of the Foreign Key relationships (without cascading). Almost reinventing the wheel, unless I misunderstand the purpose here.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 25, 2011 at 4:22 pm
I made the change on the WorkLog table, as suggested. I also noticed that there were three relationships that were linking the table with itself, so removed those too.
Unless otherwise stated, I use Microsoft Visual Studio 2005 Professional and Microsoft SQL Server 2005.
January 25, 2011 at 4:31 pm
Craig Farrell (1/25/2011)
spmaguire (1/25/2011)
Pretty much,the IsUsed column is used to check for when someone wants to delete that particular entry/row. if an existing worklog record has used a particular column within the other tables, they can't delete the item because its been used.This sounds to me like the entire purpose of the Foreign Key relationships (without cascading). Almost reinventing the wheel, unless I misunderstand the purpose here.
I see where you are coming from here. It also makes sense. Because the FK would exist if it's been used or not. More modifications to make. Thanks guys for all input.
Unless otherwise stated, I use Microsoft Visual Studio 2005 Professional and Microsoft SQL Server 2005.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply