Database structure help

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Marty
    #1

    Database structure help

    OK, I'm new at all of this and have decided to take on a project to
    learn. We have a fishing club that has 23 members. Each weak we go
    fishing in various places for 4 hours. Not together, usually 2 to a
    boat and it's your choice where to go.
    I want to build a database / website that the individual fishermen can
    go to and log there success. The fishermen can enter the fish type,
    size, weight, date caught. I only want them to add, edit, and delete
    their own information they can't mess with the data of others. On the
    web page I want to display the member name and number of fish caught
    under its respective date. Then you can click on the number and it will
    display all of the entries for that member that day. Or you can click
    on the date and it will display all the data for that day.
    How would you go about building the database? Should I have just one
    table for all or a table for each member?
    Thanks, I just don't want to jump into this and then find out I should
    have gone the other way.

    Tags: None
    • IchBin
      #2
      Re: Database structure help

      Marty wrote:
      OK, I'm new at all of this and have decided to take on a project to
      learn. We have a fishing club that has 23 members. Each weak we go
      fishing in various places for 4 hours. Not together, usually 2 to a
      boat and it's your choice where to go.
      I want to build a database / website that the individual fishermen can
      go to and log there success. The fishermen can enter the fish type,
      size, weight, date caught. I only want them to add, edit, and delete
      their own information they can't mess with the data of others. On the
      web page I want to display the member name and number of fish caught
      under its respective date. Then you can click on the number and it will
      display all of the entries for that member that day. Or you can click
      on the date and it will display all the data for that day.
      How would you go about building the database? Should I have just one
      table for all or a table for each member?
      Thanks, I just don't want to jump into this and then find out I should
      have gone the other way.
      >
      I would have three tables:

      - One for users (So you can know who is updating the table via login)
      - One for fish types
      - One for a Fish_Outing table that combines the foreign keys for user
      and fish types and any detail information for any fish caught for a
      particular outing.

      You will need the use to login to your app. This way you can allow the
      user to modify their own data. The following may get you started. I
      assume you would do it in a MySQL Database.

      User table would at least have the following rows:

      DROP TABLE IF EXISTS `Users`;
      CREATE TABLE IF NOT EXISTS `Users`
      (
      `id` int(11) NOT NULL auto_increment,
      `login_account_ name` varchar(25) NOT NULL,
      `login_password ` varchar(25) NOT NULL,
      `last_name` varchar(25) NOT NULL,
      `first_name` varchar(25) NOT NULL,
      `timestamp` timestamp NOT NULL default CURRENT_TIMESTA MP,
      PRIMARY KEY (`id`),
      UNIQUE KEY `account_name` (`login_account _name`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT= 54 ;


      Fish table to normalize what fish types can be used. It would at least
      have the following rows:

      DROP TABLE IF EXISTS `Fish`;
      CREATE TABLE IF NOT EXISTS `Fish`
      (
      `id` int(11) NOT NULL auto_increment,
      `TypeOfFish` varchar(100) NOT NULL,
      `timestamp` timestamp NOT NULL default CURRENT_TIMESTA MP,
      PRIMARY KEY (`id`),
      UNIQUE KEY `TypeOfFish` (`TypeOfFish`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT= 54 ;


      FishOuting table would at least have the following rows per fish caught:

      DROP TABLE IF EXISTS `FishOuting`;
      CREATE TABLE IF NOT EXISTS `FishOuting`
      (
      `id` int(11) NOT NULL auto_increment,
      `user_fk` int(11) NOT NULL,
      `fish_fk` int(11) NOT NULL,
      `size` int(11) NOT NULL,
      `weight` int(11) NOT NULL,
      `date_caught`Da te NOT NULL,
      `time_caught`Ti me NOT NULL,
      `timestamp` timestamp NOT NULL default CURRENT_TIMESTA MP,
      PRIMARY KEY (`id`),
      KEY `TypeOfFish` (`user_fk`,`fis h_fk`,`date_cau ght`,`time_caug ht`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT= 54 ;

      --
      Thanks in Advance...
      IchBin, Pocono Lake, Pa, USA http://weconsultants.phpnet.us
      'If there is one, Knowledge is the "Fountain of Youth"'
      -William E. Taylor, Regular Guy (1952-)

        Comment

        • Marty
          #3
          Re: Database structure help

          All I can say is "Wow thanks!"

          IchBin wrote:
          Marty wrote:
          OK, I'm new at all of this and have decided to take on a project to
          learn. We have a fishing club that has 23 members. Each weak we go
          fishing in various places for 4 hours. Not together, usually 2 to a
          boat and it's your choice where to go.
          I want to build a database / website that the individual fishermen can
          go to and log there success. The fishermen can enter the fish type,
          size, weight, date caught. I only want them to add, edit, and delete
          their own information they can't mess with the data of others. On the
          web page I want to display the member name and number of fish caught
          under its respective date. Then you can click on the number and it will
          display all of the entries for that member that day. Or you can click
          on the date and it will display all the data for that day.
          How would you go about building the database? Should I have just one
          table for all or a table for each member?
          Thanks, I just don't want to jump into this and then find out I should
          have gone the other way.
          I would have three tables:
          >
          - One for users (So you can know who is updating the table via login)
          - One for fish types
          - One for a Fish_Outing table that combines the foreign keys for user
          and fish types and any detail information for any fish caught for a
          particular outing.
          >
          You will need the use to login to your app. This way you can allow the
          user to modify their own data. The following may get you started. I
          assume you would do it in a MySQL Database.
          >
          User table would at least have the following rows:
          >
          DROP TABLE IF EXISTS `Users`;
          CREATE TABLE IF NOT EXISTS `Users`
          (
          `id` int(11) NOT NULL auto_increment,
          `login_account_ name` varchar(25) NOT NULL,
          `login_password ` varchar(25) NOT NULL,
          `last_name` varchar(25) NOT NULL,
          `first_name` varchar(25) NOT NULL,
          `timestamp` timestamp NOT NULL default CURRENT_TIMESTA MP,
          PRIMARY KEY (`id`),
          UNIQUE KEY `account_name` (`login_account _name`)
          ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT= 54 ;
          >
          >
          Fish table to normalize what fish types can be used. It would at least
          have the following rows:
          >
          DROP TABLE IF EXISTS `Fish`;
          CREATE TABLE IF NOT EXISTS `Fish`
          (
          `id` int(11) NOT NULL auto_increment,
          `TypeOfFish` varchar(100) NOT NULL,
          `timestamp` timestamp NOT NULL default CURRENT_TIMESTA MP,
          PRIMARY KEY (`id`),
          UNIQUE KEY `TypeOfFish` (`TypeOfFish`)
          ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT= 54 ;
          >
          >
          FishOuting table would at least have the following rows per fish caught:
          >
          DROP TABLE IF EXISTS `FishOuting`;
          CREATE TABLE IF NOT EXISTS `FishOuting`
          (
          `id` int(11) NOT NULL auto_increment,
          `user_fk` int(11) NOT NULL,
          `fish_fk` int(11) NOT NULL,
          `size` int(11) NOT NULL,
          `weight` int(11) NOT NULL,
          `date_caught`Da te NOT NULL,
          `time_caught`Ti me NOT NULL,
          `timestamp` timestamp NOT NULL default CURRENT_TIMESTA MP,
          PRIMARY KEY (`id`),
          KEY `TypeOfFish` (`user_fk`,`fis h_fk`,`date_cau ght`,`time_caug ht`)
          ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT= 54 ;
          >
          --
          Thanks in Advance...
          IchBin, Pocono Lake, Pa, USA http://weconsultants.phpnet.us
          'If there is one, Knowledge is the "Fountain of Youth"'
          -William E. Taylor, Regular Guy (1952-)

            Comment

            • IchBin
              #4
              Re: Database structure help

              Marty wrote:
              All I can say is "Wow thanks!"
              >
              IchBin wrote:
              >Marty wrote:
              >>OK, I'm new at all of this and have decided to take on a project to
              >>learn. We have a fishing club that has 23 members. Each weak we go
              >>fishing in various places for 4 hours. Not together, usually 2 to a
              >>boat and it's your choice where to go.
              >>I want to build a database / website that the individual fishermen can
              >>go to and log there success. The fishermen can enter the fish type,
              >>size, weight, date caught. I only want them to add, edit, and delete
              >>their own information they can't mess with the data of others. On the
              >>web page I want to display the member name and number of fish caught
              >>under its respective date. Then you can click on the number and it will
              >>display all of the entries for that member that day. Or you can click
              >>on the date and it will display all the data for that day.
              >>How would you go about building the database? Should I have just one
              >>table for all or a table for each member?
              >>Thanks, I just don't want to jump into this and then find out I should
              >>have gone the other way.
              >>>
              >I would have three tables:
              >>
              [snip SQL]

              For your selects you will want to look at the SQL syntax for 'GROUP BY'
              clause. I will let you have some fun and look up that info. You will
              need a dropdown <Selectfor the type of fish for a user to select from
              when they are inserting their data. There are more details but I will
              let you have the 'joy of discovery'.

              --
              Thanks in Advance...
              IchBin, Pocono Lake, Pa, USA http://weconsultants.phpnet.us
              'If there is one, Knowledge is the "Fountain of Youth"'
              -William E. Taylor, Regular Guy (1952-)

                Comment

                Previous template Next
                Working...