My Top Level Table is below. I only want to have one of these records for each account...CREATE TABLE [dbo].[tblAccountInformation]([OID] [int] IDENTITY(1,1) NOT NULL,[AcctNbr] [float] NOT NULL,[NoteNbr] [float] NOT NULL,[BnkNbr] [varchar](3) NOT NULL,[PrimName] [varchar](50) NOT NULL,[PrimAddr] [varchar](50) NOT NULL,[PrimCity] [varchar](30) NOT NULL,[PrimSt] [char](2) NOT NULL,[PrimZip] [varchar](10) NOT NULL,[PrimSSN] [varchar](11) NOT NULL,[PrimDOB] [datetime] NOT NULL,[CoBorrName] [varchar](50) NULL,[CoBorrAddr] [varchar](50) NULL,[CoBorrCity] [varchar](30) NULL,[CoBorrSt] [char](2) NULL,[CoBorrZip] [varchar](10) NULL,[CoBorrSSN] [varchar](11) NULL,[CoBorrDOB] [datetime] NULL,[CostCenter] [float] NOT NULL,[AcctOpenDt] [datetime] NOT NULL,[LoanType] [varchar](50) NULL,[PayoffAmt] [money] NULL,[ProductType] [tinyint] NULL,[CurrBal] [money] NOT NULL,[NoteTypeCd] [float] NOT NULL,[BKDate] [datetime] NULL,[OkToSellDt] [datetime] NULL,[CreateDt] [datetime] NOT NULL CONSTRAINT [DF_tblAccount_CreateDt] DEFAULT (getdate()),[CreateBy] [varchar](7) NOT NULL,[RetireDt] [datetime] NULL,[RetireBy] [varchar](7) NULL,CONSTRAINT [PK_tblAccount] PRIMARY KEY CLUSTERED ([OID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]GOALTER TABLE [dbo].[tblAccountInformation] WITH CHECK ADD CONSTRAINT [FK_tblAccountInformation_CreateBy] FOREIGN KEY([CreateBy])REFERENCES [dbo].[tblUsers] ([EmpID])GOALTER TABLE [dbo].[tblAccountInformation] WITH CHECK ADD CONSTRAINT [FK_tblAccountInformation_RetireBy] FOREIGN KEY([RetireBy])REFERENCES [dbo].[tblUsers] ([EmpID])
Now, my 2nd table is the many-side to the table above called Collateral.CREATE TABLE [dbo].[tblCollateralInformation]([OID] [int] IDENTITY(1,1) NOT NULL,[AccountOID] [int] NOT NULL,[CurrStatusOID] [tinyint] NOT NULL,[VehYear] [int] NOT NULL,[VehMake] [varchar](50) NOT NULL,[VehModel] [varchar](50) NOT NULL,[VinNbr] [varchar](25) NOT NULL,[VehicleColor] [varchar](50) NULL,[VehicleMileage] [float] NULL,[VehicleConditionOID] [tinyint] NULL,[AddtlComments] [varchar](100) NULL,[DealerNbr] [int] NULL,[DealerName] [varchar](100) NULL,[DealerSt] [char](2) NULL,[CreateBy] [varchar](7) NOT NULL,[CreateDt] [datetime] NULL CONSTRAINT [DF_tblAccountCollateral_CreateDt] DEFAULT (getdate()),[RetireBy] [varchar](7) NULL,[RetireDt] [datetime] NULL,CONSTRAINT [PK_tblAccountCollateral] PRIMARY KEY CLUSTERED ([OID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]GOALTER TABLE [dbo].[tblCollateralInformation] WITH CHECK ADD CONSTRAINT [FK_tblCollateralInformation_AccountOID] FOREIGN KEY([AccountOID])REFERENCES [dbo].[tblAccountInformation] ([OID])GOALTER TABLE [dbo].[tblCollateralInformation] WITH CHECK ADD CONSTRAINT [FK_tblCollateralInformation_CreateBy] FOREIGN KEY([CreateBy])REFERENCES [dbo].[tblUsers] ([EmpID])GOALTER TABLE [dbo].[tblCollateralInformation] WITH CHECK ADD CONSTRAINT [FK_tblCollateralInformation_CurrStatusOID] FOREIGN KEY([CurrStatusOID])REFERENCES [dbo].[tblStatus] ([OID])GOALTER TABLE [dbo].[tblCollateralInformation] WITH CHECK ADD CONSTRAINT [FK_tblCollateralInformation_RetireBy] FOREIGN KEY([RetireBy])REFERENCES [dbo].[tblUsers] ([EmpID])GOALTER TABLE [dbo].[tblCollateralInformation] WITH CHECK ADD CONSTRAINT [FK_tblCollateralInformation_VehicleConditionOID] FOREIGN KEY([VehicleConditionOID])REFERENCES [dbo].[tblVehicleCondition] ([OID])
Here is my 3rd table. This is a repossession tracking database and this table is related down to all the other tables in the design. This is the repo assignment table.CREATE TABLE [dbo].[tblAssignments]([OID] [int] IDENTITY(1,1) NOT NULL,[CollateralOID] [int] NOT NULL,[RepoCodeOID] [tinyint] NOT NULL,[AssignedDt] [datetime] NOT NULL,[CreateDt] [datetime] NOT NULL CONSTRAINT [DF_tblAssignments_CreateDt] DEFAULT (getdate()),[LastUpdateDt] [datetime] NOT NULL,[LastUpdateBy] [varchar](7) NOT NULL,CONSTRAINT [PK_tblAssignments] PRIMARY KEY CLUSTERED ([OID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]GOALTER TABLE [dbo].[tblAssignments] WITH CHECK ADD CONSTRAINT [FK_tblAssignments_CollateralOID] FOREIGN KEY([CollateralOID])REFERENCES [dbo].[tblCollateralInformation] ([OID])GOALTER TABLE [dbo].[tblAssignments] WITH CHECK ADD CONSTRAINT [FK_tblAssignments_LastUpdateBy] FOREIGN KEY([LastUpdateBy])REFERENCES [dbo].[tblUsers] ([EmpID])GOALTER TABLE [dbo].[tblAssignments] WITH CHECK ADD CONSTRAINT [FK_tblAssignments_RepoCodeOID] FOREIGN KEY([RepoCodeOID])REFERENCES [dbo].[tblRepoCodes] ([OID])
Here's where I'm stuck. Because there can be multiple pieces of collateral for each account, there also can be multiple assignments for Repo (each asset has its own repo assignment).If someone goes into repo today but then, catches up with their loan and comes current, he redeems the car. In 6 months, he falls behind again and repo assignments are made a 2nd time. Data from the first table like Current Balance and Payoff Amount will be different the 2nd time this car goes into repo.I could design a many-side table for this data and relate it back to the assignments table but if there are more than one assignment, that would be like entering the current balance and payoff amount more than once which would screw up all the reporting.If I go many-side back to account, how can I determine which amounts are for which assignments?Any help or assistance would be greatly appreciated... 
DB Structure Problem
Posted - 2008-08-04 : 08:52:41
Em 