Thread

Hi all,
I am seeking advice on how to best set up the collection of data. I am working with an organization that will have volunteers input data about roadkill sightings. There are 16 different animals, we are also collecting info about age, sex and location of sighting.

My question should i break the 16 different animals into 16 different table(field) entries or should I have all 16 animals entered into one master field (animals).

Will I be better off in the long run to have the data more broken down?

I have the collection forms set up using freeform with several custom fields and that end is working fine.

thanks in advance

Depends on the usage.

If you have like 500 animals each, I would definitely break that up.
Think of it like this:

1. The more complex your “WHERE” statement, the more time your database needs to load.
2. All the animals in one table is very simple. But could result in quite complex queries.
3. Keeping the animals in different tables, in theory, would result in a better maintainable database, but if you have a lot of tables in there, could get confusing.
4. If you aren’t using anything like PHPmyAdmin or so, why would you need an maintainable database, if your system is maintaining everything?

After all, you should just do whatever you like. Your database ain’t complex enough to give you hours(or even minutes) of loading time (i assume), so if you aren’t implementing this on big scale, I wouldn’t bother to much 😉.

Thanks,
I think I get it now.
I was able to use the where statement to pull the data that I want.

First steps are too break your application data down into their seperate, real world, entities. This, would be the first step towards realising what tables you require. You have: (from what I can see)

Volunteer
Animal
Sighting

Sighting would consist of a key for Volunteer and Animal, along with it’s own unique attributes for example location. You weren’t clear about age and sex, so im assuming that is a property of the Volunteer 😊

SELECT Volunteer.name, Animal.name, Sighting.location FROM Sighting JOIN Volunteer ON Sighting.volunteer_id = Volunteer.id JOIN Animal ON Sighting.animal_id = Animal.id