[RESOLVED] Checklist for collector site?

mugwumpr

I've been trying to wrap my head around this one for awhile now, and it's got me totally stumped. I'm not even sure where to start....

I'm running a collector site with a database table for "eggs" and one for "users". I've got all the display, selecting, sorting, linking and whatnot down for the eggs, and I've got a perfectly useful registration form for the users. What I'm currently lacking is a way to connect the two to produce an editable and save-able checklist so that the user can maintain a list of eggs that they have/need.

There are currently 221 eggs, and it's growing daily. The database is MyISAM (promises of InnoDB abound, but reality hasn't caught up yet). As of this moment, there is 1 user - me. No point in others if they can't save their lists.

I have a lovely checklist that does everything I want, EXCEPT save which little boxes have checkmarks in them, based on which user is logged in.

Will someone please give me a little shove in the right direction? Point me to a tutorial maybe? A book? Even an example script page that I can cannibalize or adapt.

Thank you!

😕

scrupul0us

on each user record, create a field called "collected", in that field store an array of the egg_id's the user has collected... if the number isnt in there, then they havent collected it yet and as such the checkboxes for those missing would be empty

if you dont want todo it on the user table you could create a table just for this with two fields... user_id, collected

if you want more data about each egg collected then youll want a seprate table with
collected_id, user_id, egg_id and then fields for any descriptive stuff about each (date collected, etc)... this table will be huge depending on # of eggs and users

laserlight

mugwumpr wrote:
I'm running a collector site with a database table for "eggs" and one for "users". I've got all the display, selecting, sorting, linking and whatnot down for the eggs, and I've got a perfectly useful registration form for the users. What I'm currently lacking is a way to connect the two to produce an editable and save-able checklist so that the user can maintain a list of eggs that they have/need.

What is the relationship between eggs and users? It is clear that a user can have many eggs, but must each egg belong to exactly zero or one users? Or can an egg belong to many users (i.e., it is not so much a physical egg as a type of egg, where all eggs of the type are regarded as interchangeable)?

For the former, an easy solution is to add a column to the eggs table that stores the id of the corresponding user. For the latter, create a new table that matches eggs and users.

scrupul0us wrote:
on each user record, create a field called "collected", in that field store an array of the egg_id's the user has collected... if the number isnt in there, then they havent collected it yet and as such the checkboxes for those missing would be empty

How would you store an array in a field? Clearly, you would be using a multi-valued field.

mugwumpr

Hmmm..... many things to ponder, thank you!

If it makes things clearer, I've got the list built so people can at least print it and use it that way:

Egg Checklist

I wasn't aware that you could have multiple values in one table column; I was having nightmares about 221+ columns that gradually ate up all my database resources.

:rolleyes:

bradgrafelman

mugwumpr wrote:
I wasn't aware that you could have multiple values in one table column

You can, but you definitely shouldn't - this suggests poor database design and a lack of normalization.

mugwumpr wrote:
I was having nightmares about 221+ columns that gradually ate up all my database resources.

Again, this suggests poor database design and an extreme lack of normalization. I would suggest you Google for some good Database Normalization articles/tutorials.

mugwumpr

Heh.... so if I shouldn't have them either combined or separated, what would you suggest I use?

Making the structure as efficient as possible (i.e. "normalized") was the goal that prompted my original question.

bradgrafelman

laserlight's post should get you on track towards normalization. Basically, you would separate out data into separate tables with referential data (e.g. foreign keys) that relate the data in one table to that of another.

It'd really depend on the specifics (as laserlight hinted at) in order to know what type of design (e.g. number of tables/columns) you should go for.

mugwumpr

Essentially it's many eggs collected by many members, so one member can have multiple eggs, and each egg can be owned by multiple members. Each member will have their own version of the page I posted a link to earlier.

I will likely create a new table to match eggs and members, it's just the HOW that I'm having problems with.

How can I create a table to store this info without having either:

1) many values in one field (either egg or member; use php implode and explode)
2) a separate column for each of an indefinite number of values (again, either egg or member)?

I already have done a great deal of research on this (3 weeks worth, in fact); that's why I finally tried posting here.

bradgrafelman

So it's a many-to-many relationship?

My guess would be to have three tables; eggs, members, owners. Eggs just have a primary ID key to identify the eggs and likewise for members. The owners table would have two (foreign, if you get InnoD😎 key-ed columns; egg_id and member_id. For each egg-owner relationship, you'd have a row in that table.

mugwumpr

Excellent! Thank you!!

😃

I'll try that out this weekend.