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 |
|
Onamuji
Aged Yak Warrior
504 Posts |
 Posted - 2003-01-08 : 14:42:02
|
Part of the system I am designing have a section for system notifications (this is outside notification services).CREATE TABLE NotificationEvents ( Name NVARCHAR(32) NOT NULL PRIMARY KEY CLUSTERED, Enabled BIT NOT NULL)CREATE TABLE Notifications ( NotificationID INT NOT NULL IDENTITY(1,1) PRIMARY KEY NONCLUSTERED, ProjectNumber INT NOT NULL, -- the reference the an event that happens in the project NotificationEvent NVARCHAR(32) NOT NULL REFERENCES NotificationEvents(Name), UserName NVARCHAR(32) NULL, -- some events require a user to work UNIQUE CLUSTERED (ProjectNumber, NotificationEvent, UserName))CREATE TABLE NotificationRecipients ( NotificationID INT NOT NULL REFERENCES Notifications(NotificationID), UserName NVARCHAR(32) NULL, EmailAddress NVARCHAR(256) NULL, CHECK ((UserName IS NULL AND EmailAddress IS NOT NULL) OR (UserName IS NOT NULL AND EmailAddress IS NULL)))Is there a better way to model the NotificationRecipients. The business rules state that either a user of the application or an email address may be a recipient. I'm at a loss for innovative thinking when it comes to this. HELP!  or maybe there is no other logical choice..-- updateand/or is there a better way to model the actual notifications defined by the application. Some events require a user to happen, such as USER action event, all other events are things like PROJECT_CHANGED, PROJECT_COMPLETED, etc...Edited by - onamuji on 01/08/2003 14:43:54Edited by - onamuji on 01/08/2003 14:51:26 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-01-08 : 15:16:53
|
| NotificationEvents doesn't seem to have a description.If Name is a presentation item then I would put an identity on the table and reference that.I put an id on the user table because you will probably need to hold more info at some point.Can users be added to multiple notification?UseridName varchar(256) not nullType int 0 = name, 1 = emaiol addressUserNotificationNotification_idUser_id==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2003-01-08 : 15:29:49
|
| Yes sorry, in my effort to quickly type that up I forgot some key relationships.The UserName is a foreign key to the users tableProjectNumber is also a foreign key to the projects tableYes only, one user/email address per notification, a notification is defined as a (projectnumber and event (with a user if required))does this help?Also forgot the unique constraint on the NotificationRecipients tableUNIQUE CLUSTERED (NotificationID, UserName, EmailAddress)it can't be a PRIMARY KEY because the two columns can be null, which bothers me, because up to this point I have been able to avoid NULL columns... :( |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-08 : 15:40:02
|
| I would say that in the NotificationRecipients table that you don't need both email address and username. If you get rid of one of them then you can make the remaining column not null and lose the CHECK constraint.In the Users table, is there a column for email address? Or is there a separate table for email addresses, with a relationship to Users? If there is, you could then put UserName in NotficationRecipients and then join it to get the email address. If there IS no relationship, then it makes sense to store only email address in NotificationRecipients. It is kinda weird to want to notify someone via email if the email column is nullable....is there another notification method besides email?And I may be wrong, but I don't think email addresses can contain double-byte extended characters, so you could just make email address varchar. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-01-08 : 15:45:09
|
| You could always split the Notification recipient into 2 tables...DavidM"SQL-3 is an abomination.." |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2003-01-08 : 16:01:45
|
| Ok, so the reason it was decided to store the username for the users, is that if the user changes their email address before the notification is sent, it wouldn't know the new address when it sent the email... (there are templates for notifications that get autogenerated) so that would be a pain to maintain... maybe i'll just bite the bullet and put it into two tables...also while i'm here, i'm working on a sort of form data element design that must be maintained by the database.for example (we call each for a component), each component can have children components that can inherit data off their parent components. does anyone have any suggestions i can offer to the development team on a better way to store these forms. At one point XML was suggested and then just storing the data collected as a NAME,VALUE table entry instead, then the NAME,VALUE set for a component can be returned to the application layer for integration with the component. in maybe my ignorance i suggested store the structure of the component in the database, so that we don't have 40-50 XML component forms sitting around.CREATE TABLE Components ( ComponentID INT NOT NULL IDENTITY(1,1) PRIMARY KEY NONCLUSTERED, ComponentPID INT NOT NULL REFERENCES Components(ComponentID), -- self referencing to avoid null values Name VARCHAR(64) NOT NULL CHECK (LEN(Name) > 0) UNIQUE CLUSTERED, -- yes the name changes often 1-2 weeks Description TEXT NOT NULL)CREATE TABLE ComponentElements ( ComponentElementID INT NOT NULL IDENTITY(1,1) PRIMARY KEY NONCLUSTERED, ComponentID INT NOT NULL REFERENCES Components(ComponentID), DataType VARCHAR(32) NOT NULL REFERENCES DataTypes(Name), -- the type of control to render Name VARCHAR(32) NOT NULL CHECK (LEN(Name) > 0), -- the name that the value will be stored under Label VARCHAR(64) NOT NULL, ExtendedAttributes VARCHAR(1024) NOT NULL, Required BIT NOT NULL, ValidationExpression VARCHAR(128) NOT NULL, -- the regular expression to validate the data Sequence INT NOT NULL, -- the order of the element in the form Locked BIT NOT NULL, -- if the value is inherited it can be locked Synchronized BIT NOT NULL, -- if the value is inherited it keeps it in sync with it (change here, updates source and all other uses of it) InheritsFrom INT NOT NULL REFERENCES ComponentElements(ComponentElementID), -- should have a UDF to check to make sure it is a valid inheritable element)one other suggestion that was made was to use ASP.NET user controls and register them with the database.. for instance... the form would be built using a user control and it would once completed, register its name with the database, removing the ComponentElements table, and for each form, all it need to do is call a save value procedure that accepts an XML document of <component name="UserInformation"><value name="the unique value">value</value></component> and such... any thoughts on this?sorry for taking the topics all over the place, just having a lot of ideas and trying to get other professionals opinions on them... no one here is a real good resource for this kind of stuff (everyone comes to me) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-08 : 16:45:36
|
It sounds like you're storing parts of the application in the database, is that what you're doing? If that's not it, then ignore this post.  I remember trying that with an Access app many, many years ago, and while it seemed pretty cool, it was only good for very simple structures...no hierarchy to the components or anything fancy or complicated. I ended up ditching the idea because it really wasn't worth the effort.Keeping application logic in the database is generally a bad idea. What I mean by that is the APPLICATION design should be in the APPLICATION. If there is a hierarchy to the DATA, then that does belong in the database. Trying to generalize or convert application logic so that it fits into a database usually doesn't work. I would say XML would be a much better solution for your needs, and since you don't think XML is the way to go, then I think the whole idea won't work. And no, I don't think storing the XML in the database is the way to do it either. I think the developers are either playing a trick on you or really reaching way beyond their understanding. Something like what you're suggesting is viable only if it appears very obvious and simple to do. If it is at all questionable, then it probably won't work for the application.If I'm way off, can you post a specific example of what you're trying to do? Use as much real data/info/logic as you can provide. |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2003-01-08 : 20:32:51
|
| 1) it will have to work since its a requirement of this phase of the application.The first phase was to get the base application running and functional so that the users could well, use it. This has been running fine for the past 2 years, if you consider EXTREMELY slow response time as fine (this was do to the fact that the application was linking to a MSProject 98 database for its TIMELINE data). Having removed that interaction the application is now running at 1500% more efficient than it did from phase I.Phase II, are the enhancements that were just finished, clean up the database so seperate it from MS project, unfortunately I wasn't fully involved in this design/development until very late in the process. The application got a GUI tweak and many database tweaks.Phase III, is the next set of actual physical enhancements, and taking the current FORM architecture and turning it into a much easier method.For instance, there currently is a form call UserInformation that gathers information about the user on it, this form is generally required for any other form, except a few.Then we have a form say, called "Access - Internet". But some of the elements of this form derive from the UserInformation form, such as NT login name. The users must enter this data seperately, but it really applies to a specific UserInformation form.So what we have been playing with is the idea of Sub-Forms. Forms that simply extend a base form. We have about 40-50 ACCESS REQUEST type forms available today. All of those require the user information form, however there is no way currently to tell the application that this other form is required because they are all ignorant of each other.So to make it easier on the user, the idea is to have a base form for all Access requests. Then have a sub-set of forms that the user can add to that one. So that when our Security team gets the request, they can view the appropriate information and verify it without having to look at anyone elses data (telecom requests, store repair requests, and other such forms can come on the same project) this would help them and the other members of the other teams that use the forms to collect information to perform their job.Does this make sense so far? Late night schyzophrenia might be kicking in, so bare with me.What is now back on the table, since I thought about it some more and some other people had some ideas is the whole Web Control being the form and that interacting with the database to get its data. Thus the application logic stays with the application layer and the data with the data-layer.To control the listings of forms and sub-forms, a web control will be required to have a system generated ID attached with it when it makes a request to the database. So in essence the web control's author will need to submit a RegisterForm command to the database to get back the ID it needs to use from then on.Then to ensure data-integrity and no one tries to gank other peoples data, the database could make sure that when a web control asks for data about another form, it will only allow the other forms data to be returned if that other form is a parent form in the hierarchy of forms.This will essentially save our end-users a few minutes here and there (our system processes between 50-100 requests per day, most are simple such as requesting transmittals of certification code to production or access to a particular system or application). It should reduce the form size by at least 2-5 fields per sub form.Oh and to counter attack any claim that says we should just make an add-on to the application that makes certain forms required if another form is selected and the required form is not filled out. Though this is the base need, the end-users are pushing for this new functionality, and so are the people who own the forms, it will simplify their jobs as well, saving them precious time having to make sure that the user supplied all the required forms, and if they didn't then they contact the user to have them update the request. This can delay projects days sometimes.Still with me? Any suggestions on this?I have one other question, does anyone know of a good way to store a DATE timeline... other than START_DATE, END_DATE ... this is generally how i've been doing it , but would love a compound type that could handle it, I think I mentioned the object-oriented data model. I think that's where that came from (maybe oracle). You can create a data-type likeCREATE DATATYPE DATETIMELINE ( START DATETIME; END DATETIME;)then use it like DECLARE @target DATETIMELINESET @target.START = "01/01/2003"SET @target.END = "01/30/2003"and so forth? any info on this? the oracle/informix/idms dba asked me and i told him no there isn't such a structure in MSSQL to best of my knowledge.. any plans in yukon?S-C-H-Y-Z-O-P-H-R-E-N-I-A.NET |
|
|
|
|
|
|
|
|
Architecture Design Decision