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-)
