Posted by IchBin on 09/01/06 15:33

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_TIMESTAMP,
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_TIMESTAMP,
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`Date NOT NULL,
`time_caught`Time NOT NULL,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `TypeOfFish` (`user_fk`,`fish_fk`,`date_caught`,`time_caught`)
) 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-)

[Back to original message]