Plea for help! Create Additional Table or Not

[dmontano]

I hope begging in a thread title every now and then is alright...

 

I am struggling with whether I should create a new table for Vendor Equipment in my Vendors Database.

 

My current Vendor Database has 4 tables:

vendor_companies

vendor_employees

vendor_agreements

vendor_reviews

 

I believe I need to create another table for Vendor Equipment because I would like to see all of the equipment any one vendor has.

 

I could put all the vendor's Equipment in the vendor_companies table, since their Equipment is an attribute of the Vendor Company, however, it is never just one or a few pieces of equipment. One vendor may have 4 pieces of equipment, whereas, another vendor may have 10 pieces.

 

If I were to include the vendor's Equipment in the Vendor Companies Table, I believe I would have to create the number of fields to accomodate the highest number of pieces of equipment on a vendor companies table layout - which is pretty inflexible. Am I correct in this assumption?

 

If so, it seems that a separate table is the correct way.

 

If a new table for vendor equipment is correct, would I join these two tables as such?

vendor_equipment::vendor_equipment_id" (primary key)

to:

vendor_companies::vendor_equipment_id (foreign key)

 

Then would I create a portal pulling data from the new table (vendor_equipment) to the Vendor Companies Layout to display all the equipment for that vendor? Am I correct in this assumption?

 

Is there a better way?

 

Sorry if this seems obvious - but I am still not clear about the core structuring of databases. Someone just lie to me and say I got it right!

 

Thanks in advance.

[David Head]

If so, it seems that a separate table is the correct way.

 

If a new table for vendor equipment is correct, would I join these two tables as such?

vendor_equipment::vendor_equipment_id" (primary key)

to:

vendor_companies::vendor_equipment_id (foreign key)

 

Then would I create a portal pulling data from the new table (vendor_equipment) to the Vendor Companies Layout to display all the equipment for that vendor? Am I correct in this assumption?

Hi David

You are correct in thinking that a new table is the way to go for recording equipment. If you do this, you have the flexibility of adding no equipment, one piece or many pieces for any vendor.

 

Given that equipment belongs to the company, it is right to join it to the company table. Then you go off track. Instead of joining via an equipmentID field, you would join using the CompanyID:

 

COMPANY table       EQUIPMENT table
 [b]CompanyID[/b]           EquipmentID
 CompanyName         [b]CompanyID[/b]
 Phone               SerialNumber
                     ProductType

 

Then you are right again - create a portal on the Company layout displaying records from the equipment table.

[dmontano]

Thank you very much. I am obviously getting tangled in the joining and my thinking. How long does this confusion last for the average person - or am I plagued with this handicap. It is driving me crazy.

 

My thought process "was"...

1. Creating a table of equipment with unique IDs.

2. Companies "have" equipment that I want to track.

3. Add the equipment one-by-one to the company file as they are equipped.

4. To do so, I just needed to make sure the equipment ID is unique in order to "assign" it to the company. (Do not know how just yet)

 

But what you are pointing out (I think) is that I need to assign the company to a piece of equipment in the Equipment table. I would imagine this is possible while in the Companies table (I need to think for days again on this before I post any resemblence of a reasonable question).

 

If I create a portal of equipment on the companies layout, I think I can "assign" equipment via a portal row. That would require me to enable the creation of records (if enabled in the relationship dialog)? I have been afraid to do so for lack of understanding the ramification.

 

On the flip side, I do want to be able to "unassigned" a piece of equipment via the portal, however, not delete the actual equipment from the table. More than one vendor can have the exact same type of equipment. I think I left that out. The equipment is not by part number or serial. It is a way for me to see which vendors have a 6-color printing press (for example). Many will have a 6 color press.

 

I believe my fear is "deletion" of actual equipment data that I do not want deleted, but instead want to "unassign" that equipment to that particular company. I believe I would have to enable deletion of records as well - I think that is what I want to avoid.

 

Unless, I would be creating basically duplicate equipment records in the equipment tables that just had the companyid tagged to it as well.... I know this is wrong, I think I have just jumped-the-shark...

 

Maybe I have an illness....

[David Head]

My thought process "was"...

1. Creating a table of equipment with unique IDs.

2. Companies "have" equipment that I want to track.

3. Add the equipment one-by-one to the company file as they are equipped.

4. To do so, I just needed to make sure the equipment ID is unique in order to "assign" it to the company. (Do not know how just yet)

Not too bad thinking.

1. It is good that all equipment has a unique ID - that helps to link it to other things such as service records.

2. True that companies have equipment - none, one or many pieces - that needs to be tracked

3. No, you should be adding equipment and assigning it to companies as appropriate.

4. Certainly the equipment ID must be unique but that does not help in assigning it to a company. To assign any equipment to a specific company, that equipment record must contain the unique ID of that company. This is what the foreign key (companyID) in the equipment table is used for. It will link any one equipment record to a specific company.

 

It is like writing a code on the equipment itself. You look at the item and see a company ID written on it. You go and look up the company ID in your company table. When you find the ID, you can see which company it is that owns the equipment. There is also an equipment ID on the item but that doesn't tell you who owns it. But it may enable you to look up service history for the item because each service record will contain the equipment ID.

 

To go further, to 'unassign' a piece of equipment, you can simply delete the companyID in the record. This unlinks it from any specific company. The only problem here is that you are not retaining a history of ownership. If this is important, then you need a join table (ownership) to link equipment to companies.

[dmontano]

Wow, two responses in a row. (Note to self - the begging in thread topic works. Use it sparingly).

 

David, Thank you.

 

I will let this soak in my head for a week and see where I am after I try working with it.

[dmontano]

Well, it has just gotten more complicated - for me.

 

I have been trying to figure out how to create the field structure to hold the "equipment" data and have discovered:

 

1. The equipment I am keeping a record of is general and not specific to "one" vendor or "one" industry. I can not "see" an easy way to create "general fields" in the equipment table (on one layout) that would make sense for different types of vendors, such as a bindery - who would not have "printing presses", but rather folding and stitching machinery.

 

2. So, to avoid creating "general fields" that will not work across a wide variety of vendors, I thought I would create more specific fields that are common within that vendors "industry". That will require a much larger set of fields to be created, but would give me more contextual control over what needs to be selected in this drop down fields to populate that equipment record.

 

3. But all those "more specific fields" would never fit on one layout and I would paint myself in a corner as the need WILL arise to expand the vendor industry field types. So, I thought I could create 1 layout per "vendor industry" and that would give me more than enough room to hold the data. I think this is correct - correct me if I am wrong.

 

4. Then I was thinking about how I want to pull the equipment data into a portal on the vendors layout to see what equipment they have. Then, a user could search for "any vendor that has a 40" 6 color printing press with aquaous coating" for example. Then I realized, since I did not create a generalized set of fields that would specific data - because they would be different across "industry" they would never fit on a portal row, I would never be able to have the flexibility to show as a portal line.

 

5. I thought I could create a calculation field in the Equipment table that would string together certain fields from each "vendor industry" fields and use the resulting calc field as the portal information to be displayed (kind of like a description field on an invoice). But then a user could not search for one field such as "Printing Press" as a drop down, they would have a huge long list of "strung together" field content.

 

I know there is a way to do something as I would like. Maybe in a completely different manner.

 

I think I need a join table by the way.... and aspirin.

[dmontano]

Please see the attached relationship graph that I have been messing with all afternoon. Notes are on the Relationship Graph, so I will not explain here.

 

Any review is appreciated.

 

Thanks,

David

[HBMarlowe]

1. The equipment I am keeping a record of is general and not specific to "one" vendor or "one" industry. I can not "see" an easy way to create "general fields" in the equipment table (on one layout)

 

So don't. You can have any number of layouts based on a given table. Make your layouts with only the relevant fields and give your users an easy way to navigate to the appropriate layout.

 

2. So, to avoid creating "general fields" that will not work across a wide variety of vendors, I thought I would create more specific fields that are common within that vendors "industry". That will require a much larger set of fields to be created, but would give me more contextual control over what needs to be selected in this drop down fields to populate that equipment record.

 

Still no reason for all the extra tables. To quote Allan Hunter, "fields are cheap." You can use filtered value lists for your drop down/pop ups.

 

3. But all those "more specific fields" would never fit on one layout and I would paint myself in a corner as the need WILL arise to expand the vendor industry field types.

 

Yup. Organizing the fields on a layout to ensure a smooth workflow will challenge your skills whether or not they are all from the same table.

 

Then you can create a "Search Field" (calculation, text) that concatenates as many of your fields as you like. If all those fields are in the same table the calc can be stored, making for relatively quick finds. If the fields are from foreign tables the calc cannot be stored. No good.

[dmontano]

Thanks John,

 

I was trying to envision the layout the portal or portals would reside.

 

I currently have a 7 tab control on a vendor company layout. One of these tabs is titled: equipment. I am hoping to be able to devise something that will allow me to drop a portal in that tab and display the equipment related to that vendor. With the industries requiring more specific fields, not sure how to go about it.

 

I agree that I can build the fields out in terms of how-many on the data intake side (vendor equipment layout), but how do I get the resulting vendor "equipment list" to show up in a portal on vendor companies in a way that stays within the size constraints (width) and field label specifics that could cover these different industry equipment types?

 

Is it possible to have a join 2 tables consequetively? See previous attachment as I am curious if it can be done that way.

 

I appreciate your help,

David

[HBMarlowe]

David,

 

You've raised some interesting questions in this and other threads, and I'm sure you've learned a lot of techniques along the way. It seems to me, however, that your project is short on basic planning. Questions about structure and key relationships are best answered before you ever start coding and building layouts.

 

To organize things in your own mind, and to give others a sense of what you need to achieve, take time to write a few paragraphs about what goes on in your business, as relates to the FileMaker project you have in mind. Write in conversational English, as opposed to filemakerese. Leave out jargon about tables, relationships, portals, fields, etc.

 

Include people in your organization, by job description, and the people they deal with outside your company. Who needs access to what pieces of information? Include your suppliers, and what they supply, in fairly specific terms.

 

Describe the work flow, or flows, from concept to completion.

 

Are purchase orders involved? Invoices? Is your project going to track sales (your sales to your customers)? That may seem beyond the scope of your present task, but considering it now will help you to avoid limiting your future options. It's entirely possible to structure your solution so as to add many features down the road, while focusing on a narrower aspect at present. It's best to consider the future possibilities early, even if they don't all appear in Version One.

 

What reports will you need? Keep in mind that a report can be ink on white paper, but it can also be a filtered portal on the screen. Avoid getting bogged down in trying to build a layout at this point. Layout design can be time consuming enough when your only concern is screen real estate; it's nigh impossible if the necessary data is unavailable because of faulty relationships.

 

Posting such an overview of your project for this discussion group will help people give you in depth advice. It will be much easier to see what things are entities (tables), and what are attributes (fields). From your description flows a clear Entity Relationship Diagram, which in turn leads to a functional Table Occurrence Graph.

 

Taking time to do all this before you begin, or at least before you've wasted a lot of time and aspirin, will make the whole thing come to fruition much sooner. It will also increase the chances that your solution will actually work!

 

I hope that I'm not preaching too much, David. In any case I look forward to hearing how your work is progressing.

 

Regards,

 

John

[dmontano]

You have precisely identified my problem and I am aware of it - oh so intimately. What you are asking I wish I could say and say precisely so others could understand - a case of the blind leading the sighted.

 

Your advice is rock solid and I have spent a HUGE amount of time trying to create an ER. It is only 1:45 AM, I think I will go to bed early!

 

Maybe I can wake up half dazed and confused so I can make an attempt at your advice.

 

Thank you for your reply,

David

[dmontano]

Does any one else think it would be a tremendous benefit if there was a dedicated forum topic here at the Cafe for Entity Relationships? Where troubled individuals like me could post amongst other troubled individuals.

 

Those seeking to help those in need could render first-aid with the knowledge that these posters are specifically lost in the basic fundamental aspect of proper DB construct?

 

If vendor employees are indeed an entity and "our" company employees are an entity, why not place them in a "people" entity and identity them via a field type in "people" instead. Why is my head saying - no, I want them separate. The answer may be either / or and that is a big crux for me? This type of confusion can continue on and on.

 

Should I have a table for A particular letter, or a table for ALL letters.

 

FileMaker is great. It is relatively intuitive and that in of itself has led to non-db type individuals (like me) to pick up a copy and just-do-it. That will be the "gothca" if you do not completely understand the ER data modeling process.

 

This is echoed in the advice I am getting via replies to my thread posts.

 

I can make layouts, I understand the basics of scripting, I understand the basics of portals, I understand navigation - these are implementation tools and details - I need the back-bone ER understanding.

 

I have been in and out of FileMaker for a few years, so I am not exactly a newbie. I have some awesome looking and functioning DBs - I am so impressed that users will be so conveniently able to see the time in any time zone that a coworker is working in via an auto-calc. But that is the icing on the cake - I need the cake to hold the icing.

 

The core connectivity and structure is what really matters - and it ALWAYS comes back to a solid ER.

 

So, since I am currently stuck and precluded from doing any real work - I thought I would take this opportunity - since I am currently in a state of twiddling my thumbs and sweating - and alter my "Plea for help!" to "How about a specific forum topic at the Cafe? Say, ER First Aid? ER 101? ER for Dummies? ER for those less fortunate?

 

Can I get a second vote?