SQL Server 2000 DB Design Features
-
I've been trying to find information on whether SQL Server 2000 supports the implementation of Business Rules or not. I've not found any information on this.
I've been studying DB design lately and i see many examples in SQL server books describing the type of relationships between tables such as 1:M, 1:1. but nothing showing the degree of participation. I've also searched the net. I would like to impletement Business rules and see them on the ER diagram if possible.
Here is an example of what i'm looking for:
Employee can have many Customers. (1:M) defines the type of relationship.
Business Rule states that Employee may have 0 to 25 customers at one time, as expressed as (0, 25) The 0 also shows that the Customers are Optional and not Mandatory. There is also a maximum limit of 25 Customers or records associated with each Employee. However Customer has to belong to at least 1 employee. Which is (1, 1). This is a mandatory relationship with the minimum number of employees associated with the customer being 1 and the maximum being 1.
I would also like to know what Deletion rules are supported.
I've learned about
- Deny
- Restrict
- Cascade (I know this one is supported)
- Nullify
- Set Default ( I think this one is supported)
Basically, if these things are not supported to me this would be undermining the ability to maintain integrity. If i can't restrict the number of records in a table to 25 per EMP ID, what happens when the table contains 30 or 40 customers associated with that ID, seems as if i would have to do a lot of work to make sure this never happens.
I would also like to see this info in the ER Diagram. its seems good for quick references. To me if i have to use another tool to do this, how do i know i've place all the right restrictions on the right tables?
Thanks guys!
-
Some of the more complex business rules You refer to here can be enforced with something called TRIGGERS. These are business rules that apply to a table, not to a relation per se.
You could for example on the Customers table create a trigger that checks that only 0-25 customers exist for an Employee.
You will not be able to "see" this rule in the "ER Diagram" that can be created in EM.
Deletion rules:
- Deny & Restrict ( is this not the same ? ): Yes, just create a FK between the tables
- Cascade: Yes, just create a cascading FK between the tables
- Nullify: With Trigger (what a great rule this is!, just leave the customers hanging)
- Set Default: With Trigger
In Sql Server, when You set up a reltionship between tables you cannot specify the degree of participation apart from (1(0):1(0), 1(0):M). With Triggers additional rules of arbitrary complexity can be created.
Glad someone is concerned about integrity 🙂
/rockmoose
You must unlearn what You have learnt -
Thanks for the information. I will look up some info on triggers!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply