SQLTeam.com | Weblogs | Forums

I need to fix this DB - with your help

Dear DB design experts,

As part of our continuing efforts to fix an existing app and the DB that it points to, I have two tables we have some concerns about their design.

The first table is called AppData with the following field names:

[WaterAccountNo] [decimal](15, 1) NOT NULL,
[ApplicationNo] [int] NOT NULL,
[OwnerID] varchar NOT NULL,
[InstallAddress] [int] NOT NULL,
[MailAddress] [int] NOT NULL,
[Batch_No] [smallint] NOT NULL,
[Applicant] varchar NOT NULL,
[GPFPerCommode] varchar NOT NULL,
[RebatePerCommode] varchar NOT NULL,
[TotalRebates] [money] NOT NULL,
[DateReceived] [datetime] NOT NULL,
[DateEntered] [datetime] NOT NULL

Please see screenshot of the two tables and their sample data:
edit: (screenshot removed by forum moderator at request of poster)

Of particular interest to us are (for AppData table) three fieldnames, GPFPerCommode, RebatePerCommode, and TotalRebates.

If you take a look at the attached screenshot, GPFPerCommode, RebatePerCommode fieldnames have multiple values per user.

Then TotalRebates. I have never seen a DB with Total values of anything being saved to the database.

Then on the second table called AddressInfo, there are also three fieldnames called Commodes, Replaced128 and Replaced160.

128 is actually 1.28 which is one of the sizes of a commode and 1.60 is another size.

However, this developer used those as fieldnames instead of figuring out a way to calculate them.

I hope this is not as confusing to you as it was to me.

I am wondering if these designs are ok.

As always, many thanks for your assistance.

from your screen shots, it looks like the 1st one doesn't support the second one, so it's hard to understand what you have. The comma separated values is not good at all, should be separate rows probably in another table. Need some more info with relevant DDL and sample data.