|
|
Posted by Marty on 09/01/06 17:00
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_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-)
Navigation:
[Reply to this message]
|