August 6, 2003 at 5:32 am
VB.Net (mostly, some VC++) and ASP.NET are our main links to SQL Server.
We are struggling between two competing philosophies. We have a LOT of tables and databases all encompassing some of the same business concepts. So for example a Customer Number appears in LOTS Of places. We want to make sure we use consistent data typing whereever it appears.
User defined datatypes are a natural within SQL, so people don't have to know it is an INT vs. DECIMAL(10,0) or whatever.
However, I'm getting resistance from the programmers who then find it annoying when writing programs to access the tables, they can't use the same datatype, and (to them) it somewhat obscures what the underlying physical representation needs to be.
How are people approaching this? Anyone written code generators to turn SQL UDT's into various language equivilents that can be included? Or do you just skip the UDT's entirely for this reason?
On a related note, is anyone tring to use VS.NET/VSS to manage SQL scripts as parts of projects, or do you just do it outside in VSS directly?
August 6, 2003 at 6:53 am
I am afraid I can't really help you on this. We have used UDT's in some projects (using VB.NET), but we did not have a direct link to the type in the code.
We did have one class for each database object to encapsulate / hide everything from the developper. So this circumvents to some extent the problem of not knowing the exact physical representation.
One problem we did run in to was increasing the length of a UDT varchar definition. This was impossible, so you have to drop and recreate everything, which is cumbersome, to say the least.
August 6, 2003 at 6:58 am
We dont use UDT's either. The abstraction is nice, but change is painful. I'll admit that UDT's shouldnt be changing, but sometimes they do. So far not managing scripts in .Net.
Andy
August 7, 2003 at 6:34 am
What exactly do you mean by "Scripts"? DBScripts?
August 7, 2003 at 6:49 am
Re Scripts: From my perspective yes, any thing related to the DB. I insist that the entire database structure be maintained in source safe in scripts to the point it can be rebuilt if needed (and we test that periodically). To me that is the "source" of the database, also where many things need to be documented (e.g. stored procedures). But more importantly it is the history and audit of changes, so when you find this "thing" in your database and want to know why and how and when and who... you have something.
August 7, 2003 at 7:07 am
Ok, just making sure I am on the same page. What I've done is created a methodology of Database versions.
I've created one main DBScript which scripts in the Database, Users, Logins, etc. anything that is universal to the Database.
Additionally, I keep a Script of each Table in a subfolder. When I make any changes to that Table, I carefully modify the Script so that it will be able to handle updating itself no matter which version it's being upgraded from. Of course, I have run into a few snags with this but I have something in the works to make this really easy later on.
August 7, 2003 at 1:08 pm
UDT's are great when modeling a database. Having said that I don't use them in any of my databases as I prefer to use the base data type. This lessens the possibily of error when coding against the database and that to me is more important than using a UDT.
As far as scripting the database goes. I usually have one script with the tables, views, indexes, and keys and then separate scripts for all the triggers, functions, and procedures. I then put all of these in source control and update them as needed.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
August 8, 2003 at 8:43 am
We do use UDT either. I think it is better to use strong Entity/Attribute data modeling software and let the foreign key migration enforce the data conformity. For non-keys we use LOGICAL ONLY UDT but translate them to standard data types in the physical model. We use ERwin. It is a little pricey but I think there are others out there for less.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply