One to Many Question

[cover]

For lack of a better input method, I'm thinking of a form with many
input fields (i.e. 25) for users to enter equipment related
information. As I think about connecting and entering into a MySQL
database, 25 fields or so on one table seems like a lot so I am
thinking about breaking it up into categories. For example the
database name=equipment and tables for pumps, conveyors, shakers, and
'other'. Any thoughts on this folks? I could do the table name as
equipment_tbl for example with its 25 fields but am a little concerned
with how the physical size of this database *might* grow. Feedback is
appreciated. TIA

[Andy Jeffries]

cover wrote:

For lack of a better input method, I'm thinking of a form with many
input fields (i.e. 25) for users to enter equipment related
information. As I think about connecting and entering into a MySQL
database, 25 fields or so on one table seems like a lot so I am
thinking about breaking it up into categories. For example the
database name=equipment and tables for pumps, conveyors, shakers, and
'other'. Any thoughts on this folks? I could do the table name as
equipment_tbl for example with its 25 fields but am a little concerned
with how the physical size of this database *might* grow. Feedback is
appreciated. TIA

Personally, I wouldn't worry about 25 fields, it's not a lot. But bear
in mind future growth (maybe individual tables might be better) against
ease of extraction (multi-table joins for lots of tables).

Cheers,
Andy

[Ian B]

Not too easy to advise you when all you say is that you have 25 (or so)
fields of equipment like pumps and shakers, but...

from what you have said, I would be looking at

table :equip
equip_uid
equip_type_uid
equip_info

table: type
type_uid type_name

You add a type (say 1,"Pump")

and you add an equip (say 1,1,"Sludge")

from that, you know that the equip record is about a pump ( because its
equip_type_uid = 1 and you can look that up in the type table and find
that type_uid of 1 has a description of "Pump"

Your problem with multiple fields is that if you ever need to search,
you have 25 fields to look in, not one. This way, it is easy to search
(SELECT * FROM equip WHERE equip_info LIKE '%sludge%') and the type
table makes it easy to create dropdown boxes, search by category (no
misspelt "pmup") and you will probably save space, and if you rename
"Pump" to "Liquid Transfer Engines" you only have to do it in one place
(likewise, if you have a Spanish site, you only need another table
"type_es" with 1,Bombe" and all the equipment has Spanish categories.

Ian