Too many fields?

[customfamily]

Hi all,

I'm a novice and am wondering is it smart to create a database with several hundred fields in a single table? What problems could I be facing in the long run? I'd really appreciate any and all opinions on this question.

Thanks,

Rob

[Anuviel]

I am not sure but from what I learned here, it seems that you might have a data structure issue if you need that many fields in one table.

There might also be some issues with speed, getting all those fields out on a layout while keeping it effective for the end-user...

* Just my thoughts.

[bcooney]

I agree. In my twenty-something years (I started at age 6), I've never built a system with several hundred fields in one table. @OP: Sounds like your data model is not correct.

Edited July 7, 2009 by Guest

[Søren Dyhr]

Thou shalt not... Well the theoretical angle to this is:

http://en.wikipedia.org/wiki/Database_normalization

However would I suggest you pick inspiration form this:

http://www.filemakermagazine.com/videos/data-tagging-classification-vs-organization.html

--sd

[customfamily]

Thanks all. I thought so.

Just to clarify: we're a production company and come in contact with a very broad selection of vendors, artists, talent, locations, models, clients and photographers. We have to be able to find the owner of a vintage cadillac we used a few years back in the same database that would have contact info for a penthouse apartment a client would as a location.

These contacts would need vastly different categorization options but in essence, they are all contacts and whatever they're characteristics, for us, in essence, they mean a phone number to call.

So I'm wondering what would be the best way to organize this expanding database. Currently we have contacts and locations in 2 separate tables and was thinking of adding on tables for talent and props.

What would be a smart way to keep all contact info in one place, regardless of category, but be able to diversify all other information?

Hope this makes sense.

Thanks so much for all your help.

R.

[LaRetta]

Personally, I do not think it is the number of fields which matters but rather the type of data they hold. As long as it is not multiple 'like' fields then it is probably fine and I HAVE been involved with client databases that HAVE had several hundred fields in it and they did not break a single normalization rule.

Sometimes you can also group a set of fields and put it as a 1:1 (one-to-one) relationship just to keep the database more compact. But if you split off a group of fields, be sure that they are not fields which will be required in a relationship with the main record.

[LaRetta]

Ask yourself the 'one to many' question:

Will this Contact have more than one phone number? Yes, then phone numbers should be a related table.

Will this Contact have more than one birthday? Hopefully not hga ha ha ha, so it should go in the Contact table.

Is this registration date ever going to change (like when the Contact re-registers)? Yes, then it should be multiple records in a Registrations table so you can track their prior registration.

Addresses, separate, numbers separate (can be phone numbers, fax numbers, cel etc in simple NUMBERS table). Business has multiple websites? Websites table. If you want to know about anything specific, just ask ... and think ahead. Just because now you don't think you would have more than two Vendors, don't put Vendor information in same record - allow for expansion and growth and change.

You can always use a normalized, expanded structure even if isn't totally necessary yet - but re-structuring to allow for expansion is PAINFULLLLLLLL.

Edited July 7, 2009 by Guest

[LaRetta]

What would be a smart way to keep all contact info in one place, regardless of category, but be able to diversify all other information?

This is a fine-line, judgement call. Hard drive space is cheap so having some empty fields isn't an issue. But if 100 of the 150 fields are NEVER the same for the Contacts, split them (as I indicated 1:1), having the Contact base information and Category in one table and their 'category-specific' details in another.

Edited July 7, 2009 by Guest

[Anuviel]

I would try something like this

A contact table - contains all fields that are common across all contacts such as address and phone for example.

A category field in the contact table which would let you select to which category the contact belongs to.

A Category table/s related to contact table.

The category table/s could then contain fields specific to each category.

Maybe something along those lines...

[customfamily]

Amuviel, I have been thinking of a solution along the lines you have suggested.

Being that I'm a newbie and am working with a database which already contains data, I have been having trouble connecting 2 databases the way you have suggested.

Previously, contacts and locations had already been assigned serial numbers in 2 separate databases. I've had a tough time making relationships work across tables because of this, although I'm sure that my lack of expertise is also a problem.

I suppose the way it would work is that after a new record (unique serial) is created in the contacts database, after a category is chosen, a new records with matching serial will be automatically created in the corresponding table (based on category selected). Am I right?

[Anuviel]

Yes, that would be correct. That is how I would go about it / have it work.

Do note that I am new as well when it comes to FM and some people who replied to this post are experts.

[customfamily]

Then let's hope they give us some more input :D

[bruceR]

Hi all,

I'm a novice and am wondering is it smart to create a database with several hundred fields in a single table? What problems could I be facing in the long run? I'd really appreciate any and all opinions on this question.

Thanks,

Rob

You're trying to create a complex and vitally important business system; and you recognize that you are a novice. You're catching on to a some of the important ideas, but you're a long ways from mastering them - you might want to hire an experienced developer. Sure, I'm one of them; but experienced help can get you much farther much faster.

[customfamily]

I know, I know...the problem is we have had 3-4 developers attempt to create a database that would suite our needs, some of them we're specialized in creating systems for the photo/production world and they all failed miserably. So we realized that we have no choice but do our best on our own.

I've given it a few months and am happy with what we've accomplished but agree that more knowledge would lead to a better database. So my question is this: would I be able to create a complex layout system and once done have a developer build the "infrastructure" for the layout? In other words: I create the visuals, a professional does the behind the scenes work?

Thanks again,

R.

[bcooney]

I would be surprised if the developers that you used were FBA members or at the least, Certified.

You could certainly create sample screen shots, and have a developer build a system from that. It's one way to communicate, especially if you are very "visual" oriented. The caveat may be that FM can't accomplish the interface you hope for, and so there might be some compromises. Also, sketch out reports that you need, as that's a good way to work backwards to what data fields you'll need to capture.

[mr_vodka]

I know, I know...the problem is we have had 3-4 developers attempt to create a database that would suite our needs, some of them we're specialized in creating systems for the photo/production world and they all failed miserably. So we realized that we have no choice but do our best on our own.

I am more than sure that most of the true developers that are on the boards could help you with building your system. :D

[bruceR]

I know, I know...the problem is we have had 3-4 developers attempt to create a database that would suite our needs, some of them we're specialized in creating systems for the photo/production world and they all failed miserably. So we realized that we have no choice but do our best on our own.

Complete non-sequiter. WHO failed miserably to do what? Perhaps they even did what you asked for.

I have no idea one way or the other about the competence of the developers you've already tried, but you've said that your very first step was to create a bazillion field un-normalized system; and this is better?

The development process definitely involves learning and change on both sides, and communication. I still think you probably need to find the right development partner, and your screenshot idea may be helpful.