Jump to content
Salesforce and other SMB Solutions are coming soon. ×

A question about files


Wingwalker
 Share

Recommended Posts

Hi everyone,

 

I am building a new solution that I have on the planning stage on paper. Looking at it brings up a question I thought you can help me with. I have planned an order entry system with a Company, Vendor and Contacts file and I’m wondering if I really need all three.

 

All companies and vendors have the same things in common with addresses, people and phone numbers, right. Each will have a unique ID Number and you can easily identify a Company from a Vendor. So it’s a simple matter in a Contacts file to filter one from the other in a portal for ease of selection. So why have all three files? Why not just a contacts file?

 

I know that Vendors requires a product file and that would be a separate file. I also know that an Invoice file with line items is also necessary and is also a separate file. So my question is, why have a separate Customer and Vendor File? Why not just a Contacts file?

 

I’ll appreciate your comments,

 

James

Link to comment
Share on other sites

I agree totally: one table (table not file; you don't need a separate file for any of this in FileMaker 7 or 8, because each file can contain as many tables as you need; older versions did indeed need separate files for each table) for vendors and companies and contacts, with a checkbox or radio button to differentiate between them. You probably would want a relationship of that table back to itself so that a company (let's call it Company A) can have a list of contact people who work at Company A, each of whom has their own "contacts" record, so you can bring up the Company A record and see the various contact personnel in a portal at the bottom or some such thing.

 

But yeah, I'd do it in one table.

Link to comment
Share on other sites

Although I'm a strong proponent of keeping like-data in the same table, I think this may be a case where those three entities should be kept separate. In my opinion, these things are likely to have different attributes (fields) as the solution develops. This is especially true with Company and Contact, where a Company might have many Contacts. And while a Vendor may have many attributes in common with a Company, there would also seem to be many things that are needed for one but not the other. For example, with Vendors, you may have a few relationships to an Invoice or Product table, and maybe some aggregate calcs associated with those. The Company entity may also have relationships to an Invoice table, and maybe also have some aggregate calcs associated with them, but these relationships would not be using the same keys as the Vendor entity in the Invoice table.

 

While these entities could be combined in one table using filtering techniques to keep the data straight, I think such a table would be harder to maintain than three separate tables. With having several fields that are only used for one of the entities, having to filter most of the relationships to other tables, and then having to filter the Finds and Reports, I just think it would be easier on the sanity to keep things separate.

Link to comment
Share on other sites

Hey there James,

 

Although it greatly pains me to say so… Ender is right! Now if you ever repeat I said that I would categorically deny it. But in this instance the boy is absolutely correct, succinct and at the top of his game!smiley-smile

 

That’s not to say that Ahunter is wrong because he isn’t! It’s just that you will be adding to the complexity of your design using the direction of Contacts only. As Ender pointed out, while these entities could be combined in one table using filtering techniques to keep the data straight. The table would be harder to maintain than three separate tables.

 

I also like to keep like-data in the same table and it seems a good idea at first blush and frankly, can be, depending on what you’re doing and what you know. But like many things with FileMaker, you can do a lot of things a lot of different ways depending on what you want to accomplish and your degree of knowledge.

 

We hope all this helps… Give us a shout back if it doesn’t or you need more information. We have a motto here on the Café. “If we can’t help, we can at least confuse!smiley-wink

 

Harry

Link to comment
Share on other sites

I'd like to put my two cents worth in here as well. I made the mistake of using one table in one of my recent products and all was well initially. Then my customer wanted to add a field here and there and it quickly became a nightmare. I started trying to ID fields by using a leading C_XXXX for customer related fields and S_XXXX for supplier related fields and CS_XXX for when it didn't matter, but over time I ended up dumping it and starting over, it just got too confusing... The BIG benifit of FM 7 and 8 is being able to have multiple tables with many relationships and while it may take a little more time upfront to design with seperate tables, in the end it's worth it...

 

Just my two cents...

Blevey

Link to comment
Share on other sites

Hi Blevey I'm glad you jumped in,

 

If I can maybe add a little to what we’re all saying and read between the lines a little bit. Most of us don't work on our solutions all that often. We build it, make modification to it, generally modest and then we let it do what we build it to do. Some of us do a better job of notating what we did and why we did it than others, making the jobe a lot easier. But smart money plans for expansion. I can’t remember a client saying "Ok, let’s chop this in half"!

 

Recently a client that I designed a solution for some years ago called and wants additions made. This is a solution I've not looked since I delivered it five years ago. It’s one of those times that I sit back and pat myself on the back for things I DIDN’T DO!smiley-wink

 

Look, the evolution of FileMaker forces changes and yes, some to the structure of the way we do things. But whatever we do needs to be duplicateable, it needs to be "Oh ya, I remember this" because that's how I do it. That’s what I was trying to say to James and Blevey; you did a better job of it by your example. If there is one way that offers great flexibility verses another that may be slicker, quicker and made of whicker.smiley-tongue-out Give some real thought to the flexibility side of the issue and when you think you really have it down-pat… Put it on paper and write out your plan, similar to a blueprint for a house. It doesn’t take that much time but oh the difference it will make to your solution. Plumbing is really meaningless until you need water right!smiley-smile

 

I’m still working on my telling Ender that he was right… Hey, maybe he wont come back to this thread again. Well, I know he will thou, my being his hero and all!smiley-laughing I guess if nothing else I can use the old line, “Ya you got the idea from me”! You think he’ll buy that one again? Shuuuuuuuuuuuuuure he will!smiley-surprised

 

Harry

Link to comment
Share on other sites

Hi Blevey I'm glad you jumped in,

 

If I can maybe add a little to what we’re all saying and read between the lines a little bit. Most of us don't work on our solutions all that often. We build it, make modification to it, generally modest and then we let it do what we build it to do. Some of us do a better job of notating what we did and why we did it than others, making the jobe a lot easier. But smart money plans for expansion. I can’t remember a client saying "Ok, let’s chop this in half"!

 

Recently a client that I designed a solution for some years ago called and wants additions made. This is a solution I've not looked since I delivered it five years ago. It’s one of those times that I sit back and pat myself on the back for things I DIDN’T DO!smiley-wink

 

Look, the evolution of FileMaker forces changes and yes, some to the structure of the way we do things. But whatever we do needs to be duplicateable, it needs to be "Oh ya, I remember this" because that's how I do it. That’s what I was trying to say to James and Blevey; you did a better job of it by your example. If there is one way that offers great flexibility verses another that may be slicker, quicker and made of whicker.smiley-tongue-out Give some real thought to the flexibility side of the issue and when you think you really have it down-pat… Put it on paper and write out your plan, similar to a blueprint for a house. It doesn’t take that much time but oh the difference it will make to your solution. Plumbing is really meaningless until you need water right!smiley-smile

 

I’m still working on my telling Ender that he was right… Hey, maybe he won't come back to this thread again. I know he will thou, my being his hero and all!smiley-laughing I guess if nothing else I can use the old line, “Ya you got the idea from me”! You think he’ll buy that one again? Shuuuuuuuuuuuuuure he will!smiley-surprised

 

Harry

Link to comment
Share on other sites

I would like to pose another question along these lines to help me understand when separate tables should be used. What if you have a table of Proposals and a table of Contracts that have most of the same information, such as date of first contact, source of original lead, key people involved and so on. When a contract is awarded, all the information in the Proposal table applies to the Contract and needs to be copied over. Would it not make sense to have only one table with a single indicator field to say whether it was a proposal or a real contract? It would seem good not to have two sets of layouts for screens and summary reports - and if you did need different portals, for example, you could set up a self join by type. The clincher is that changing a proposal to a contract would be changing one field. Does this make (database) sense?

Link to comment
Share on other sites

These are excellent questions (don't let Harry try to take credit for them), and they should be asked whenever you're debating whether to reuse a record or make a new one for the new purpose. The best solution really depends on the specific case.

 

In the case of a Proposal that becomes a Contract, or a Quote that becomes an Invoice, if it's desirable to keep the information about the original Proposal or Quote intact, then it's probably better to use separate records (and maybe separate tables) for each.

 

The question often comes up about a good way to deactivate records that are no longer needed or infrequently accessed. While it's tempting to move such inactive records into an Archive file, there's more drawbacks to this than advantages. I always recommend marking the records as "Inactive", or something similar. Then by filtering the Finds and relationships, you can get your screens and reports to only show the "Active" records by default.

Link to comment
Share on other sites

... if it's desirable to keep the information about the original Proposal or Quote intact, then it's probably better to use separate records (and maybe separate tables) for each.

 

So can I assume that if the opposite is true, then it's probably better to use the same table? I saw an application that used the same table definition for four different purposes. It had a few extra fields that only applied to one of the uses but it did a lot with what was a very simple database. This discussion makes me think of the joke about a hiring ad which was "wanted: young man with 20 years of experience". In laying out these tables and relationships it would sure be good to have 20 years of experience. I'm just trying to keep my design on the pavement as they say. I certainly agree with your recommendation of archiving by just flagging the records. I never did like the idea of deleting records that might someday need to be restored.

Link to comment
Share on other sites

We use one table for clients, potential clients, non-client "rolodex" / "anybody" contacts, and vendors. Many vendors are also clients of ours, and potential clients become clients once we start actually doing business with them.

 

We have a separate table for contacts that are child records of clients, but I've been thinking of concatenating all their info into the clients table, creating a new category "contact", and replacing the relationship to client contacts table with a selfjoin. No real reason not to: Everyone (whether they be client or contact of a client or vendor or babysitter) has a name, address, city, state, zip, primary phone, aux phone, cellphone, fax, email, and a comments field. Clients happen to also have a client code, a separate billing address/city/state/zip, and a place to enter an authorization to not charge them tax, but those fields don't get in the way for the other records. Vendors happen to also have a vendor category field. Not much else differentiates the records except the field determining what kind of record it is, categorically (reg. client, potential client, vendor, rolodex, ).

 

It would be less practical to do it this way if the data we were keeping for different types of entry differed considerably more than this.

 

But in a world where the end users don't necessarily know how a given entity might be categorized, doing a Find is a lot easier this way. And moving data between tables every time a hardware vendor becomes a client (or we start buying supplies from an existing client) would be a mess. So for us a single-table solution was totally the way to go.

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share



×
×
  • Create New...

Important Information

Terms of Use