Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Advice on Structing

    Hi guys I need some advice on the structure of this database?
    The database is called Department Stats. Now what happens is the depts go into the database and input thier stats at the end of the month. The departments will either enter in the number of times they have done a task or how many hours it will take. Should the these tables be joined or what is the best way to handle this??? there is a total of 11 tables actually

    Code:
    CREATE TABLE [dbo].[Audits](
    	[AuditID] [int] IDENTITY(1,1) NOT NULL,
    	[ScheduledAudits] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_ScheduledAudits]  DEFAULT ((0)),
    	[ComplianceReviews] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_ComplianceReviews]  DEFAULT ((0)),
    	[SpecialInvestigations] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_SpecialInvestigations]  DEFAULT ((0)),
    	[P&PReviews_Approvals] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_P&PReviews_Approvals]  DEFAULT ((0)),
    	[VCMICSReview_Approval] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_VCMICSReview_Approval]  DEFAULT ((0)),
    	[MarketingPromo_ReviewApproval] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_MarketingPromo_ReviewApproval]  DEFAULT ((0)),
    	[GamingRules_Review&Approval] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_GamingRules_Review&Approval]  DEFAULT ((0)),
    	[ContractReview&Approvals] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_ContractReview&Approvals]  DEFAULT ((0)),
    	[Background_FinancialKeyReview_KeyEmployees] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_Background_FinancialKeyReview]  DEFAULT ((0)),
    	[BackgroundRenewApproval] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_BackgroundRenewApproval]  DEFAULT ((0)),
    	[BackgroundFinalSuitApproval] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_BackgroundFinalSuitApproval]  DEFAULT ((0)),
    	[ConstructionProjectReviews] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_ConstructionProjectReviews]  DEFAULT ((0)),
    	[ConstructionProjApproved] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_ConstructionProjApproved]  DEFAULT ((0)),
    	[ConstructionPlanReviews] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_ConstructionPlanReviews]  DEFAULT ((0)),
    	[CertificatofOccupancyIssued] [numeric](18, 0) NULL,
    	[EnvirHealthInspections] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_CasinoEnvirHealthInspections]  DEFAULT ((0)),
    	[SpecialProjects] [numeric](18, 0) NULL,
    	[EnvironmentalHealthNoticeofViolationIssues] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_EnvironmentalHealthNoticeofViolationIssues]  DEFAULT ((0)),
    	[PublicHealthPermitIssud] [numeric](18, 0) NULL,
    	[DailyReviewOfKeyWatcherAccess] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_DailyReviewOfKeyWatcherUsage]  DEFAULT ((0)),
    	[DailyReviewofVar_ReconcilProcess] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_DailyReviewofVar_ReconcilProcess]  DEFAULT ((0)),
    	[MonthlyReviewOfKeyWatcherUsage] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_MonthlyReviewOfKeyWatcherUsage]  DEFAULT ((0)),
    	[Monthlytestcagesheets_JournalEntries] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_Monthlytestcagesheets_JournalEntries]  DEFAULT ((0)),
    	[MonthlyreviewFinancialRpts] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_MonthlyreviewFinancialRpts]  DEFAULT ((0)),
    	[MonthlytestgeneralLedgerAcctReconcil] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_MonthlytestgeneralLedgerAcctReconcil]  DEFAULT ((0)),
    	[MonthlyLostAndFoundAudit] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_LostAndFoundAudit]  DEFAULT ((0)),
    	[QuarteryReviewOfKeyWatchAssigned] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_QuarteryReviewOfKeyWatchAssigned]  DEFAULT ((0)),
    	[QuarterylyInventory&ReconDrop_CountKeys] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_QuarterylyInventory&ReconDrop_Counts]  DEFAULT ((0)),
    	[ConsultCasinoOps_Admin_Acct_Mktng_GmingComplIssues] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_MonthlyVariance_ReconcilationProcess]  DEFAULT ((0)),
    	[Liason_CPAFirm] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_ProjDevCasinoRiskMange]  DEFAULT ((0)),
    	[LiasonState_FedAgency] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_ProjReviewProposedState_FedRR]  DEFAULT ((0)),
    	[MonitorTitle31Compliance_Training] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_MonitorTitle31Compliance_Training]  DEFAULT ((0)),
    	[ProjDevCasinoRiskAsstment] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_RandomTestCageSheets_JournalEntries]  DEFAULT ((0)),
    	[ProjReviewProposedState_FedRegRequirmnts] [numeric](18, 0) NULL CONSTRAINT [DF_Audits_MonthlyReviewOfFinancialRpts]  DEFAULT ((0)),
    	[Start_Date] [datetime] NULL,
    	[End_Date] [datetime] NULL,

    Code:
    CREATE TABLE [dbo].[Surviellance](
    	[TableGames] [numeric](18, 0) NULL CONSTRAINT [DF_Surviellance_TableGames]  DEFAULT ((0)),
    	[SlotOperations] [numeric](18, 0) NULL CONSTRAINT [DF_Surviellance_SlotOperations]  DEFAULT ((0)),
    	[CashOperations] [numeric](18, 0) NULL CONSTRAINT [DF_Surviellance_CashOperations]  DEFAULT ((0)),
    	[Surviellance] [numeric](18, 0) NULL CONSTRAINT [DF_Surviellance_Surviellance]  DEFAULT ((0)),
    	[FoodandBeverage] [numeric](18, 0) NULL CONSTRAINT [DF_Surviellance_FoodandBeverage]  DEFAULT ((0)),
    	[Secuirty] [numeric](18, 0) NULL CONSTRAINT [DF_Surviellance_Secuirty]  DEFAULT ((0)),
    	[Jackpots] [numeric](18, 0) NULL CONSTRAINT [DF_Surviellance_Jackpots]  DEFAULT ((0)),
    	[RiskManagement] [numeric](18, 0) NULL CONSTRAINT [DF_Surviellance_RiskManagement]  DEFAULT ((0)),
    	[TableGames_CR] [numeric](18, 0) NULL CONSTRAINT [DF_Surviellance_TableGames_CR]  DEFAULT ((0)),
    	[SlotOperations_CR] [numeric](18, 0) NULL CONSTRAINT [DF_Surviellance_SlotOperations_CR]  DEFAULT ((0)),
    	[CashOperations_CR] [numeric](18, 0) NULL CONSTRAINT [DF_Surviellance_CashOperations_CR]  DEFAULT ((0)),
    	[FoodandBeverage_CR] [numeric](18, 0) NULL CONSTRAINT [DF_Surviellance_FoodandBeverage_CR]  DEFAULT ((0)),
    	[Security_CR] [numeric](18, 0) NULL CONSTRAINT [DF_Surviellance_Security_CR]  DEFAULT ((0)),
    	[Bingo] [numeric](18, 0) NULL CONSTRAINT [DF_Surviellance_Bingo]  DEFAULT ((0)),
    	[Phone_Calls] [numeric](18, 0) NULL CONSTRAINT [DF_Surviellance_Phone_Calls]  DEFAULT ((0)),
    	[Maintenance] [numeric](18, 0) NULL CONSTRAINT [DF_Surviellance_Maintenance]  DEFAULT ((0)),
    	[SurveillanceID] [int] IDENTITY(1,1) NOT NULL,
    	[Start_Date] [datetime] NULL,
    	[End_Date] [datetime] NULL,
     CONSTRAINT [PK_Surviellance] PRIMARY KEY CLUSTERED


    Code:
    CREATE TABLE [dbo].[StateLicensing](
    	[NewApplicantsSenttoState] [numeric](18, 0) NULL CONSTRAINT [DF_StateLicensing_NewApplicantsSenttoState]  DEFAULT ((0)),
    	[RenewalsSenttoState] [numeric](18, 0) NULL CONSTRAINT [DF_StateLicensing_RenewalsSenttoState]  DEFAULT ((0)),
    	[Denials] [numeric](18, 0) NULL CONSTRAINT [DF_StateLicensing_Denials]  DEFAULT ((0)),
    	[Revocation] [numeric](18, 0) NULL CONSTRAINT [DF_StateLicensing_Revocation]  DEFAULT ((0)),
    	[Suspension] [numeric](18, 0) NULL CONSTRAINT [DF_StateLicensing_Suspension]  DEFAULT ((0)),
    	[Notifications] [numeric](18, 0) NULL CONSTRAINT [DF_StateLicensing_Notifications]  DEFAULT ((0)),
    	[UpgradesToKeyPosition] [numeric](18, 0) NULL CONSTRAINT [DF_StateLicensing_UpgradesToKeyPosition]  DEFAULT ((0)),
    	[StateKeyTermination] [numeric](18, 0) NULL,
    	[StateRptID] [int] IDENTITY(1,1) NOT NULL,
    	[Start_Date] [datetime] NULL,
    	[End_Date] [datetime] NULL,
     CONSTRAINT [PK_State] PRIMARY KEY CLUSTERED 
    (

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Desi - look at your DDL - all the tables' DDL is incomplete. Please can you add the bits missed off the end?

    By "JOINED" do you mean set up relationships and foreign keys? We can't really say that with only three tables. It is not just if there should be relationships, but what those relationships should be. You would also need to handle all the orphaned data that might well be present.

    I hate retro-repairs
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Nevermind thanks anyways
    Last edited by desireemm; 04-15-09 at 17:23.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •