4 Replies Latest reply on Apr 5, 2012 3:43 PM by davidanders

    New Database

    Annette

      Title

      New Database

      Post

      Hello and thanks for taking the time to read this.  I am a newbie at database creation and I just wanted to run something by here to see if I was on the right path.  I'm at the very beginning phase of determining the info i need to collect and am about to create the different tables.  Am I correct to have the following tables:  

      Clients: 
           ClientID
           FirstName
           LastName
           FullName
           DOB
           Age
           AgeGroup
           Street
           Town
           County
           Phone
           Mobile
           Pin
           MedicalCard
           Diagnosis
           Database
           Parent/Guardian
           GuardianAddress
           GuardianPhone
           DCA
           CarersAllowance
           RespiteGrant
           DisabilityAllowance
           RespiteHours
           BehaviurDifficulties
           PhysicalDifficulties
           IntellectualDisability
           ReferralDate
           DateReceived
           FileNo
           DayPlacementProvider
           HomeHelpProvider
           ResidentialProvider
           CentreBasedProvider
           Priority
       
      Providers:
           ProviderID
           ProviderName
           Address
           Phone
           ContactName
           ContactPhone
           Email
           Agency
           ProviderType




      Diagnosis:
           DiagnosisID
           Diagnosis



      Service Info: (Would this be similar to line items?)
           Date
           TimeIn
           TimeOut
           TotalTime
           Overnight
           More to be added
       
      My hope is that once I have the tables created correctly and set up the appropriate relationships (Another area
       I'm sure I will have questions on when I get there) that I will be able to create various forms appropriate to the
      service type so that different service types can collect different information as appropriate.  I just don't want to get into
      the creation of forms until I have the basics correct.  Do I appear to have made the tables correctly?  
       
      Thanks in advance for your help!! 

        • 1. Re: New Database
          philmodjunk

          There's often more than one way to structure your data.

          Consider that both clients and providers have nearly identical contact fields (name, address, phone, etc.)

          You may want to create a contacts table that stores the contact info for both clients and providers in the same table. A field in this table can identify which are clients, which are providers (and it's not unheard of for a provider to also be a client...)

          Consider how many phone numbers, email/social networking addresses a person can have. You may find it useful to define a separate table of phone numbers (and maybe email/etc addesses) so that a flexible number of these numbers can be recorded for a single contact.

          • 2. Re: New Database
            Annette

            Ok, so I can create a contact table with has all the phone numbers and emails etc for everyone and add a field for contact type where i can label them, I'm assuming linked by a relationship by the ID number of the person...is that correct?  

             

            But the providers I have are companies, not an individual person, so would I still have them on one table with clients? 

            • 3. Re: New Database
              philmodjunk

              Yep. Keep in mind this is an option to consider as you plan your database. Often companies and people can be managed in the same table, but you don't have to. The Guardian contact info could also be recorded in this table.

              And you might put phone numbers in a table separate from but related to contacts to facilitate recording multiple contact numbers..

              • 4. Re: New Database
                davidanders

                It is easier to put things together than take them apart.

                PhoneArea
                PhoneNumber
                PhoneExtension
                for instance can avoid the data entry problem of formatting a phone number (xxx) xxx-xxxx ext xxx

                Address01
                Address02
                AddCity
                AddCounty
                AddState
                AddZIP
                AddZIP+4
                is my preference for ease of reading and formatting the Address Data to your requrements.

                You have clients, referrers, parents, day provider, residence provider, etc
                I would look at your most complex and simplest examples, the complex will tell you more...

                Note Fields should be included in whatever form your data entry people think they need.

                Two addresses? Vacation home? work number, home number, cell number, fax number, vacation phone, emergancy phone???