5 Replies Latest reply: Oct 4, 2012 4:00 AM by BrendanP RSS

    Database Design Help

    ssk1974
      Hi,

      I currently have the following database design and trying to make it normalized.

      Table Name Columns
      Program Program-ID, Program-Name, Program-Info, Person-ID(FK to Person)
      Person Person-ID, FName, LName, Addr1, Addr2, City, State, Zip, Phone1, Phone2, Email

      Along with making the above relationship normalized, I also want to maintain change history. Please see if my below model looks ok, and please advise on what corrections I need.

      Table Name Columns
      Program Program-ID, Program-Name, Program-Info, Effective_Date
      Person Person-ID (1 to Many to Person-Name ) (1 to Many to Person-Contact ) (1 to Many to Person-Address)
      Person-Name Person-ID,Name-ID
      Name FName, LName, Effective_Date (1 to Many to Person-Name )
      Person-Contact Person-ID, COntact-ID
      Contact Contact-ID,Phone1, Phone2, Email, Effective_Date (1 to Many to Person-Contact )
      Person-Address Person-ID, Address-ID
      Address Address-ID,Addr1, Addr2, City, State, Zip, Effective_Date (1 to Many to Person-Address)
      Program-Contact Program-ID, Person-ID, COntact-ID, Address-ID (1 to Many to Person-Address) (1 to Many to Person-Contact)


      If I have one to many to Person-Contact is that enough to get the name too?

      Thanks for your time and help.
        • 1. Re: Database Design Help
          sb92075
          We speak SQL?
          Do you speak SQL?

          Please post CREATE TABLE statements.
          • 2. Re: Database Design Help
            rp0428
            >
            Along with making the above relationship normalized, I also want to maintain change history. Please see if my below model looks ok, and please advise on what corrections I need.
            >
            I suggest you create some sample data, some sample tables and put the data in the tables and then see if what you have is workable.

            For example, you have a 'Person' table with a person-id that is 1 to many to person-name.

            So the same person-id can have different names?

            But then you say than the 'name' table is also 1 to many to person-name.

            It looks like you are trying to keep a separate history table for each individual attribute of an entity.

            A person is a person is a person whether they are a 'contact' or not so why are you treating contacts separately from 'person' instead of as an attribute of person? Your friends are 'persons' too aren't they, even if they are also contacts?
            • 3. Too Little Information
              Frank Kulash
              Hi,
              ssk1974 wrote:
              Hi,

              I currently have the following database design and trying to make it normalized.

              Table Name Columns
              Program Program-ID, Program-Name, Program-Info, Person-ID(FK to Person)
              Person Person-ID, FName, LName, Addr1, Addr2, City, State, Zip, Phone1, Phone2, Email
              All you've shown are table and column names. Normalization isn't about what the tables and columns are named; normalization is concerened with what the tables and columns contain, and how they are related to each other. Before anyone can know whether the design is normalized or not, or whether it's good or bad in any other respect, they have to know what business reality is being modelled, what the application is trying to do, how will it be used, and how each table and column helps the application work.

              For example: What does the person_id column in the program table represent? Is it the director of the program (and each program can have, at most, one person)? If so, that part looks good. Is that column supposed to be showing participation in programs, where many people can be participate in the same program? If so, it doesn't look so good.
              Along with making the above relationship normalized, I also want to maintain change history. Please see if my below model looks ok, and please advise on what corrections I need.

              Table Name Columns
              Program Program-ID, Program-Name, Program-Info, Effective_Date
              Person Person-ID (1 to Many to Person-Name ) (1 to Many to Person-Contact ) (1 to Many to Person-Address)
              Person-Name Person-ID,Name-ID
              Name FName, LName, Effective_Date (1 to Many to Person-Name )
              Person-Contact Person-ID, COntact-ID
              Contact Contact-ID,Phone1, Phone2, Email, Effective_Date (1 to Many to Person-Contact )
              Person-Address Person-ID, Address-ID
              Address Address-ID,Addr1, Addr2, City, State, Zip, Effective_Date (1 to Many to Person-Address)
              Program-Contact Program-ID, Person-ID, COntact-ID, Address-ID (1 to Many to Person-Address) (1 to Many to Person-Contact)
              Do you really have 9 different tables just to handle change history for the 2 tables containing current data?
              If I have one to many to Person-Contact is that enough to get the name too?
              Sorry, this question is unclear.
              Describe what business entities are involved, and how these entities correspond to tables.
              Describe what attributes are associated with the entities, and how these attributes correspond to columns.
              Describe what the application will do, and how the tables and columns you've planned will help it do that.
              • 4. Re: Database Design Help
                Billy~Verreynne
                sb92075 wrote:
                We speak SQL?
                Do you speak SQL?

                Please post CREATE TABLE statements.
                Database designs/data models are SQL indifferent. SQL is about implementing and using a data model. Not about whether the model's design is sound.

                SQL is irrelevant in this case. CREATE TABLE statements are premature.
                • 5. Re: Database Design Help
                  BrendanP
                  A diagram would be a lot easier to comprehend than the text you posted (although unfortunately you can only post text here).