Is this table to big? 2

[gusbrunston]

I'm converting a property management system from PICK to Access. Before I get beyond the point of repair... I have a table containing fields to describe properties: address, bedrooms, baths, garage, number of parking spaces, etc. Included with the other fields are 45 (may grow to 60) fields I call "amenities" (air-conditioned, balcony, ceiling fan, self-clean oven, wall-to-wall carpet, etc.) Each of these fields is true or false based on a check-box control. Is the fact that there are so many fields in one table a good reason to make a separate table for the "amenities"? I'd like to avoid separating the tables, because it would take me probably four hours to figure out the relationship of the "tblAmenities" to the "tblProperties": 1 to many? 1 to 1? Many to Many? Argh!
Your advice, as always, would be helpful.
Gus Brunston (an old PICKer)s-).

 

[RickSpr]

I wouldn't think breaking the table in two was necessary; 60 is not a huge number of fields, especially if they're all Yes/No fields.

But if you decide you want to break it, duplicate the primary key field(s) in your Amenities table. The relationship will then be one-to-one because the primary keys are identical.

When you want the basic data without amenities, use the main table as your recordsource. When you want the amenities as well, use a query that joins the main table and the Amenities table on the common key. Rick Sprague

 

[ghubbell]

Good morning Gus!
100% with Rick here. 60 fields isn't so bad...geeze you're allowed 200+ right? As Rick states though, if half of the fields are "vacant" you may want to pull them out and link them one to one. It's really just a space saver. You could get down right fancy and make the table of amenities with an "amenityID" and a description and a table of properties with a propertyID and the basic info, then the "PropertiesAmenities" table which would hold the propertyID (many) and the amenityID (many). That's the formally correct way to do it but depending on your real needs, might not be worth the effort. What's really important is to figure what you want to do with the data later. A good foundation will hold the whole heap up a lot longer and better than a weak one.

Oh, if you chose the latter, you can strip data from your big table pretty quickly using some append queries set to unique values.
Voila! Gord
ghubbell@total.net

 

[gusbrunston]

Gord and Rick:
Thank you both very much. Since the number of records will probably never exceed 1000, and someone else is going to end up maintaining the system and database, I'm going to leave the amenities in the properties table for the "ease of query." Besides, we woke up to over 10" of snow in Denver this morning--no time to divide controls into separate tables!
Thanks again.