Normalization question

Hello,

I'm building an OLTP employee roster application, and I have the data model normalized, but I'm finding it challenging to update the data in all of the underlying tables via a web form and stored procedures. I'm getting the direction to keep things normalized, but I'm struggling with table updates.

Is it sound OLTP database practice to write records to a "roster_data" table that is a foreign key repository for all of the underlying tables (almost like a view without the update restrictions)? It would be much easier to manage this table of foreign keys (fact table) instead of writing several stored procedures to keep all of the related tables (dimensions) updated. (almost like a star schema in OLAP)

I know I'm being vague, but I would like to bounce off some OLTP experts.

Thanks,

Jim

What type of data is being changed?

Is it key data?

Would CASCADE UPDATE work?

More information, table DDL, etc., would be useful.

|||

Not that you are a beginner in normalization topic, but the following links should get you the idea in thsi regard:

http://www.informit.com/articles/article.asp?p=27785&rl=1 & http://www.sqlmag.com/Articles/Index.cfm?ArticleID=4887

|||

Everyone,

My basic issue was keeping my underlying normalized data model updated via one VIEW, and I was running into the "can't updated more than one base table issue." After some research, I found the INSTEAD OF triggers -- this functionality will enable me to update all of my underyling tables using one VIEW.

Thanks everyone and have a great holiday!

Jim

Non value-type parameters in Assemblies/ CLR UDFs:

I am building a simple utitility to be hosted as a database assembly in SASS 2005. This is a .Net Assembly]

My method is date dependent and I want to pass in a DateTime rather than a string (globalisation issues.. you know the story). Can I do this and how would I pass this parameter in an MDX query?

I also want to pass in such thigs as the StorageMode enum etc. Presumably I can pass in the integer value... (kinda defeats the reason for using enums though)

Update to this, I find that you can pass the DateTime as a string and SSAS does some sort of implicit cast. this is still very sensitive to date string formatting, so is not ideal!|||

Well, it seems that after due consideration I have to answer my own post!

I think the problem is that we can only really pass in basic value types via text in queries, essentially strings and numbers. If I really want to pass in a class/struct I could pass in a string representing the XML Serialized class. For example, if I want to pass in a DateTime rather than a string, I could create an XML version of the DateTime structure. Of course, the alternative for dates would be to specify a universal format, such at the ISO format (yyyy-mm-dd) and ensure that this is rule is always adhered to, but this is coding by convention rather than using strong typing.

Now, my only problem is how to return data in a strongly typed format.... now the XML serialisation seems to be the answer to this one...