Hi Paul, I would recommend the following article from MSDN: http://msdn2.microsoft.com/en-us/library/Aa479086.aspx
Specifically, have a look at table 1's "extensibility patterns", where they go through a few options such as having preallocated fields, separate tables for each data type, etc. Most of them are a little bit nasty, and I'm not sure there is anything that I would call an excellent solution. Essentially their "Name value pairs" pattern is exactly what you are doing, perhaps just a little bit more normalized. I would definitely recommend shipping out the Name and DataType columns to a separate table (and leaving a foreign key behind) to keep things smaller, since this table is likely going to have a large number of records. I have had to use this approach on a number of occasions, and it can work OK. I would recommend creating some kind of OO abstraction that allows you to access these fields in a really easy manner, possibly also making it easier to write a screen which is auto-generated based on the fields the customer has specified. Regards, Daniel. -----Original Message----- From: Discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED] On Behalf Of Paul Cowan Sent: 05 October 2007 10:17 AM To: [email protected] Subject: [ADVANCED-DOTNET] Data Migration Hi all, I have the following problem and really could do with some design advice. I have inherited an ASP application that will be migrated to .NET for various reasons. The problem I have is that the product has say several customers and the old developers created a different database for each customer. Each database contains relatively the same table structure. What is disturbing me is that if for example we have a contacts table in each database, the fields for each contacts table are totally different. Now there are various business reasons for this. As you would expect, they have a different code stream of ASP files for each customer as each the UI needs to be different for each say Contacts page to reflect the different table structure. Also the database is the most un-normalised database ever created with every table basically having no primary key, and all null fields. Referential integrity is not here. Now I want to try and amalgamate all code streams and all databases into one stream if I possibly can. I just cannot work with the overhead of several code streams and several databases. Releasing any changes would be a nightmare. Keeping with the Contacts example I am thinking of having a contacts table with all the common fields and maybe a generic table that contains the name of the field the value of the field and the datatype of the field. I can then use this information to generate the UI perhaps. Performance concerns me. My generic table would look something like ContactUid (PK) uniqueidentifier Name nvarchar(100) Value nvarchar(100) DataType nvarchar(50) I am concerned about this approach not least as I am having to write a SSIS migration script to bring this data into a new structure. What I want to know is if there is a better way than this? I can just smell trouble but do not know what else to do.Can anyone advise on this troublesome situation. Cheers Paul [EMAIL PROTECTED] _________________________________________________________________ Celeb spotting - Play CelebMashup and win cool prizes https://www.celebmashup.com =================================== This list is hosted by DevelopMentor(r) http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com =================================== This list is hosted by DevelopMentor® http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com
