Database design - multiple apps/single table?

[lothos123452000]

Hi:

 

We are combining alot of old applications which each have their own tables and structures within those tables...not really compatible enough to combine straight out...but they could all use the "text" or similar sql server type and be put into one or two "super" tables with a type to differentiate them.

 

On one side, this would vastly simplify our schema and allow the data to be centralized. On the other, its not intuitive and could be confusing if we fall behind with our database schema documentation. But more importantly, having all of these applications hitting these one or two tables puts a fair amount of stress on our server.

 

This table might look something like this:

 

Application 1 is type 1

Application 2 is type 2

Application n is type n

 

Text1--Text2--Text3...--Textn--Type

val1 val2 val3 valn 1

val1 val2 val3 valn 2

val1 val2 val3 valn n

 

Any thoughts of this issue would be appreciated.

 

Thanks!

 

Lothos

[PlausiblyDamp]

If the applications have different requirements then trying to fit their data into one or two tables is probably going to result in either all data types being the lowest common denominator (strings) or run time errors when it turns out one type can't be converted to another correctly.

 

If they have data in common it may ultimately be worth designing an appropriate schema to consolidate and tidy the data correctly; in the short term you could also create stored procs or views that mimic the old format of data when returning it to the client - this way you can at least minimise the initial changes required to implement this migration.

 

In your post above were the table / column names meant as place holders for real values or did you intend to actually name the columns Text1, Text2 etc?

[lothos123452000]

Thanks for the response!

 

The column names are what I was toying with using...which is pretty much useless for any type of intuitive meaning for someone just looking at the database...which I would normally never do. To complicate this, they were originally done in separate schemas because it was appropriate...they aren't intrisically linked...except under a higher umbrella which is certainly to high to develop a schema underneath.

 

The more I look at this, the more I don't like it...it pretty much voids any accepted database design rule I have ever heard.

 

I think I will leave the core tables intact...seems the prudent course.

 

Thanks!

 

If the applications have different requirements then trying to fit their data into one or two tables is probably going to result in either all data types being the lowest common denominator (strings) or run time errors when it turns out one type can't be converted to another correctly.

 

If they have data in common it may ultimately be worth designing an appropriate schema to consolidate and tidy the data correctly; in the short term you could also create stored procs or views that mimic the old format of data when returning it to the client - this way you can at least minimise the initial changes required to implement this migration.

 

In your post above were the table / column names meant as place holders for real values or did you intend to actually name the columns Text1, Text2 etc?