Hi Group,

I've just started learning MySQL and trying to do what my book says
and plan on paper first. Trouble is if you don't know how things fit
together further down the line its difficult to plan.

I am trying to design a form that would be used when visiting someones
premises to undertake a survey and give advice.

My current paper based form lists a number of leaflets which can be
left. One of my tables lists these with the columns leafletid &
leafletname. My paper based form has a tick box to indicate which
have been left and I'm (at this stage) unable to see how I need to
link the main visit table to the leaflets table to indicate which has
been left. Basic stuff I'm sure.

If I used a multi choice dropdown in my final form I guess I could
just include leafletid in my main table but having all leaflets
available displayed with a check box has left me stumped as to how the
selection choice should be recorded, does it just get added to the
leaflets table, does that work?

All advice appreciated

DonH

donh wrote:
> Hi Group,
>
> I've just started learning MySQL and trying to do what my book says
> and plan on paper first. Trouble is if you don't know how things fit
> together further down the line its difficult to plan.
>
> I am trying to design a form that would be used when visiting someones
> premises to undertake a survey and give advice.
>
> My current paper based form lists a number of leaflets which can be
> left. One of my tables lists these with the columns leafletid &
> leafletname. My paper based form has a tick box to indicate which
> have been left and I'm (at this stage) unable to see how I need to
> link the main visit table to the leaflets table to indicate which has
> been left. Basic stuff I'm sure.
>
> If I used a multi choice dropdown in my final form I guess I could
> just include leafletid in my main table but having all leaflets
> available displayed with a check box has left me stumped as to how the
> selection choice should be recorded, does it just get added to the
> leaflets table, does that work?
>
> All advice appreciated
>
> DonH
>

Don,

Why not do it just as on the paper form? Put a checkbox for each
leaflet on your form, also.

Then have a third table containing two columns - premises_id and
leaflet_id. When you leave a leaflet at a premises you add an entry to
this table.

Also, you can google for "database normalization". Understanding the
various normalization forms can help with database design.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On 10 Aug, 11:39, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> donh wrote:
> > Hi Group,
>
> > I've just started learning MySQL and trying to do what my book says
> > and plan on paper first. Trouble is if you don't know how things fit
> > together further down the line its difficult to plan.
>
> > I am trying to design a form that would be used when visiting someones
> > premises to undertake a survey and give advice.
>
> > My current paper based form lists a number of leaflets which can be
> > left. One of my tables lists these with the columns leafletid &
> > leafletname. My paper based form has a tick box to indicate which
> > have been left and I'm (at this stage) unable to see how I need to
> > link the main visit table to the leaflets table to indicate which has
> > been left. Basic stuff I'm sure.
>
> > If I used a multi choice dropdown in my final form I guess I could
> > just include leafletid in my main table but having all leaflets
> > available displayed with a check box has left me stumped as to how the
> > selection choice should be recorded, does it just get added to the
> > leaflets table, does that work?
>
> > All advice appreciated
>
> > DonH
>
> Don,
>
> Why not do it just as on the paper form? Put a checkbox for each
> leaflet on your form, also.
>
> Then have a third table containing two columns - premises_id and
> leaflet_id. When you leave a leaflet at a premises you add an entry to
> this table.
>
> Also, you can google for "database normalization". Understanding the
> various normalization forms can help with database design.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================- Hide quoted text -
>
> - Show quoted text -

Thanks Jerry, I shall try both your suggestions

Manythanks

DonH

In our last episode, <1186738659.199540.238880@j4g2000prf.googlegroups. com>,
the lovely and talented donh broadcast on comp.databases.mysql:

> Hi Group,

> I've just started learning MySQL and trying to do what my book says
> and plan on paper first. Trouble is if you don't know how things fit
> together further down the line its difficult to plan.

> I am trying to design a form that would be used when visiting someones
> premises to undertake a survey and give advice.

> My current paper based form lists a number of leaflets which can be
> left. One of my tables lists these with the columns leafletid &
> leafletname. My paper based form has a tick box to indicate which
> have been left and I'm (at this stage) unable to see how I need to
> link the main visit table to the leaflets table to indicate which has
> been left. Basic stuff I'm sure.

This is not a mysql question.

> If I used a multi choice dropdown in my final form

Paper does not have dropdowns. So what kind of form are you talking about?
Is you question about HTML. About PHP or CGI? Ask in the appropriate
group.

> I guess I could just include leafletid in my main table but having all
> leaflets available displayed with a check box has left me stumped as to
> how the selection choice should be recorded, does it just get added to the
> leaflets table, does that work?

The right mysql field type is SET. You have a fixed number of different
leaflets, right? You know all the possible leaflets that can be left,
right? You are recording what combination of those leaflets are left for
each event: it might be none, it might be any one, it might be any two, and
so forth, right? The mysql field for that sort of data is SET.

SET is not right if one event could involve leaving 3 of leaflet C
and 2 of leaflet B, if how many of each leaflet is important. SET is
right if which of leaflet A, B, and C are left without regard to number.

How you collect and process that data before you present it to mysql is
something you should ask about according the frontend you are using.

> All advice appreciated

> DonH

--
Lars Eighner <http://larseighner.com/> <http://myspace.com/larseighner>
Countdown: 527 days to go.
Owing to massive spam from googlegroups, I do not see most posts from there.