Should I have used a Join Table?

[dmontano]

I have 4 native tables in 1 database file called "vendors_database". Tables in this database are called:

 

1. vendor_companies

2. vendor_employees

3. vendor_reviews

4. vendor_agreements

 

These tables have primary and foreign keys and all is working correctly from a relational standpoint - within the context of just those 4 table.

 

Now enters the broom stick that is sending me over the handlebars:

I want the ability to use another database file - our "employees" - to tie our employees into the "vendor_database".

 

For example, I want to "assign" one of our employees to be the publisher of the "vendor_companies" table, and a different employee to be the administrator of the "vendor_companies" table. Sounds simple.

 

I have the system working in my current set-up, but I believe I have done this the hard and wrong way.

 

I created a file reference to the external file - our "employees" - and created a table occurence of our "employees" inside the "vendors_database". This should be correct, however, the manner in which I have created and tied the relationships via multiple TOs based from this external table seems to be A LOT of work to do what I want.

 

In order for me to get our employees to be assigned as the publisher for just one table, I created a TO in vendor_database and related it as such:

"employee_db::employee_idkey" (primary key)

to

"vendor_companies::vendor_company_table_publisher"

 

In order for me to get a different employee to be assigned as the administrator for just one table, I created a TO in vendor_database and related it as such:

"employee_db::employee_idkey" (primary key)

to

"vendor_companies::vendor_company_table_administrator"

 

I think what I have done is related a primary key (employee_id) from employees - to a field in the "vendor_companies_table" that is acting like my foreign key in my mind - but it really isn't becasue I keep having to create an additional TO and create the "fake" foriegn key set-up. I am speculating - and grasping for straws...

 

As I said before, I got this to work, but I really do not want to continue developing ANY database in a manner that is flat out wrong.

 

Each table in "vendors_database" has different publishers and administrators. So is this a many "employees" can be placed many "times" in a table? A many-to-many relationship?

 

I have repeated this madness enough to come to the conclusion that there must be a simpler way. So I am tapping out and looking for advice, clearance, guidance, criticism.

 

Fire away!

[LingoJango]

When you say "each table" do you mean "each record" (i.e. each vendor)?

 

That aside, whether this is right or wrong depends on how many of these functions you're going to have there and how "structural" to the database or dynamically expandable you want them to be.

 

What do you need to do with them in terms of calcs & scripting? Are you ever going to want to list all the functions performed by an employee for any or all vendors? I'd say a join table is absolutely in order in that case.

 

But wait for the big guns before you decide. :-)

[dmontano]

Hi Lingo

 

To answer your first question: No.

One of our employees will be assigned as the "publisher" of the Vendor Companies which will include all vendors in that table.

 

Another employee will be assigned as the "administrator" of the Vendor Companies which will include all vendor in that table.

 

Note the two employee assignments will be for the all of the records in that one table.

 

For example:

John Doe is the Publisher, and Mary Sue will be the Administrator of our "Vendor Companies". All of these vendor company records will be held in one table: vendor_companies_table.

 

Next, Bill is the Publisher and Bob is the administrator of the "Vendor Non Disclosure Agreements. All of these vendor Non Disclosure Agreements records will be held in one table: vendor_ndas_table.

 

and so forth.

 

As for your second paragraph, it's probably best for me to describe that I am creating each "major" entity as a separate db file.

 

1. Vendor Database (4 tables specific to that entity, as described above)

2. (Our) Employees

3. (Our) Offices

etc.

 

I am struggling with the syntax and context when trying to structure an ER diagram - and this is really my big problem. I am seeing my work unravel before my eyes.

 

I do not think it is going to be hard to correct nor time consuming as I have no real data in anything as of yet. Strictly developing right now with test data.

 

I am searching to get put on the right track and it is difficult to explain.

 

The earlier help I received from you about the stacked key id field / name field has helped considerably.

 

I do understand the importance of the key ID, struggling with how to implement it in different scenarios.

 

If writers have writers-block - I have a severe case of brain-block on this subject.

[LingoJango]

Sounds like you're ok with what you've got. You might want to get some outside input on your ER - try posting it.

[dmontano]

I have attached a PDF.

 

Note the TOs color coded Blue on the right - I am referring to those.

 

I am using those as an example for me to "learn" the right way, so I can fix other instances like this elsewhere.

 

I tried working on a join table in a more simpler DB I have - and found that it was only working with portals.

 

Any help is appreciated.

[LingoJango]

Okay, here we go.

 

- First, I spotted a couple of unusual-looking relationships - _name_full fields in the nda table linked to _key_id fields. That may be junk DNA (changed field content but didn't update field name), but you might want to fix it.

 

- Second, someone (was it David Head) was recently giving advice on how many files to use. Try to find it and read it. It sounds to me as though you're building something VERY big for your very first project. Using several files increases the chances that you'll need to reconstruct major sections of your diagram.

 

- Third, I missed the all-important "and so forth" in your message. Now I see you have publishers and administrators for: Review, NDA, Employee, and Company. In addition, you have mailed_to, recipient, mailed_by and representative.

 

On this last point, the question is again, how much expansion do you expect? Will there be further links from Vendors to Employees, or not?

 

If you expect this thing to expand, consider a join table so you don't have to change the layout(s) every time you add one of these things: use a portal and you're all set.

 

If you think it won't expand, you can leave it as is -- particularly, I would say, if you plan to keep the files separate, since this way you'll have all the relevant info in the vendors table if you need to get employee info from the perspective of a different file (e.g., you're in the machinery file and for some reason you need to list the name of the NDA publisher).

 

Depending on how involved the tasks or responsibilities involved in Publishing and Editing are, since you say you'll have a single person handling each for every table, you could save quite a lot of space by using global fields there - but only, as I said, if you plan to never, ever, ever decide that Joe will publish (whatever that is) for 50% of vendors and Michelle will act as publisher for the other half.

 

Special Edition draws a clear distinction between an ER diagram and a relationship graph: an ERD is what you draw (on paper, unless you have better tools) at the planning stage. The authors even suggest little symbols for different types of relationships. If you don't have it on paper, you should. If you do and you've been changing things around, go back to the ERD and change whatever needs changing. Thinking through what is an entity and what is an attribute of an entity will help. Getting advice from the senior guys here will help even further.

 

And now I really have to stop giving you good second-hand advice and go back to taking it myself - I need to revise my own ERD before my last major effort (fingers crossed).

 

Caveat emptor, as always, and good luck with the machinery.

[dmontano]

Hi Lingo, thanks for hanging in there with me. I am going to come back and answer the questions you rasied here. In the meantime, I am going to post a new thread that is similar to this but with a different angle. I do not want to post what I have thought up in this thread as it will be sure to confuse anyone in this threads context.

 

I will respond to this as you have obviously spent enough time to "see" what I may be doing. I will post tonight when I get home.

 

Thanks a BUNCH!

[dmontano]

Hi Lingo,

 

Let me try to clear some things up, response to your questions...

 

RE: First (The unusual relationships)

They are being handled in a "like manner" as the PUBLISHERS and ADMINISTRATORS of each table. I am glad you saw that as that is the area of concern I have. I do want to fix it - can not figure out what I am suppose to do.

 

For example, on the Non Disclosure Agreement (NDA) layout (vendor_nda table) I have 2 fields that require vendor employee names - they are:

 

1. Name of VENDORS employee who we are MAILING the NDA to;

2. Name of VENDORS employee who SIGNED the contract.

(They may be the same vendor employee, often times different people)

 

I have this type of "scenario" in many instances - whether it is the vendor's employees or our employees that I am trying to assign to the record.

 

For example, similarily, on the Non Disclosure Agreement layout (vendor_nda table) I have 4 fields that require "OUR" employee names - they are:

 

1. Name of OUR employee who MAILED the NDA;

2. Name of OUR employee who SIGNED the contract.

3. Name of OUR employee who is the PUBLISHER (this is Global to vendor_nda table)

4. Name of OUR employee who is the ADMINISTRATOR (this is Global to vendor_nda table)

 

(For 1 & 2 above, they may be the same employee, often times different people)

 

What I need to be able to do, is assigned "many" people to any given layout.

 

RE: Second (Many files versus one or just a few files)

I did give this strong consideration and posted a thread here and got some feedback. I think I was able to reference the thread on David Head's comment. I am not sure - as you can see from my current situation if I have made a grave error - or if I am on the right track.

 

Very big system is relative, so I am not sure what the right answer is, except to say I envision a handful of "systems" that pulls data from whichever module it requires. I am trying to be open-ended. My updated Relationship Graph has a note explaining a little more.

 

I went "modular" also because I had trapped some of my data in previous dbs. Lookign back now, I realize the data was trapped in just one table, whereas multi-table, single file would have made it more flexible. I may have went to far overboard by creating file separation. I do think I am okay, I have all priviliges in sync and am working via FM and IWP with mulit file now. You will notice that the Vendor file has native table entities that are specific to vendors. Or employees, our offices are separate and that does help in terms of security at least in the eyes of those allowing access to their "data domain" like Human Reosurces for example.

 

Your advice is very sound, and I will continue to think about what I have done - hopefully coming to a clear understanding of what it should be - then I will move the model I "somewhat" see. Now I am doubting myself - great.

 

RE: Third (and so forth)

That's my way of telepathically getting all who read to instantly understand what I am trying to say.

 

I would imagine I see a great deal of expansion, but more importantly I do not know exactly where it will end, nor do I think it ever will. So, my thoughts are to build modular enough to allow flexibility. Sounds good, but maybe I am digging a hole.

 

Regarding the Global use for Pubs and Admins - that is exactly what I am doing and will do. The only time it would change, is when I reassign the task to a different person. Then I want all the records in that table to reflect who the "new" publisher is - for example.

 

Regarding the ERD and relationship graph. I am struggling BIG TIME. Obviously those who know how have cut their teeth and this is second hand, but I am going to attest it is not easy, nor intuitive for me.

 

For example: A Vendor Company has many employees; A employee works for only one company. I have that set up correctly - I think. However, why can't I assign more than one vendor employee to a record on the vendor nda layout? I tried tying it to the foriegn key in the NDA table, but evry time I selected the second employee, the first changed to match the second and vice-versa. So do I really have a many-to-many? And now I require a join?

 

I have revised the relationship graph and tried to clear up the names, it is attached.

 

Thanks,

David

[dmontano]

Alright Lingo,

 

I have done as asked by you and many others. I am starting to fell like I am getting well known as the one who is a little sloowww. Not trying to be difficult - it is just difficult for me. So, this is after hours of thinking and getting up and pacing and sweating some more.

 

It may not directly illustrate my original question, because that question led to you (and others) pointing out my need of a vaild ER. So I will post it here and hope it eventually leads me back to my initial dilemma.

 

Any suggestions on what I have wrong / right / chage / edit whatever....

 

Any help is always appreciated and USED!! Or at least I really try.

[LingoJango]

Hi,

 

You're not slow, you're just starting off with a very biiig project that keeps expanding as you work.

 

You would probably do well to sit down and consider whether the ERD you posted is going to cover everything you need here. You don't want your project to balloon as you're building it or you'll make the wrong choices.

 

Also, your ERD is in terms of FM tables, but it's a stage prior to table creation - it's supposed to get you to think about real-world entities rather than computer entities. For instance, I have a serious problem with the idea that one *piece* of equipment can be assigned to many vendors - perhaps one *type*, but hardly one *piece*, in this ignorant person's view, unless the equipment is something like a nuclear reactor with lots of vendors involved. Maybe you're right in viewing it this way, but in that case we need to know more about this relationship.

 

Why separate tables for Printing, Binding and other special types of equipment? What makes them special? Is there more or different info to keep track of there, compared to forklifts or to each other? I'm not saying it's the wrong choice but maybe you could have a single equipment table.

 

Start by answering the big question - what is this database solution all about? Not just the module you're currently working on, but the whole thing. For instance, I see no mention of invoicing - is that a deliberate choice or is it a postponement of decisions? I also see no mention of clients - same question. Will an NDA have to be linked to a client at some point in time?

 

I don't understand the statement "A Vendor NDA Letter has many Employees" - are they vendor employees, then?

 

Also, I suspect there may be some missing entities here - have you chunked down all the nouns relevant to vendor reviews and to equipment? When you mention "tracking" equipment, does that include things like maintenance schedules, or simply keeping track of the piece of equipment, its date of purchase and its date of disposal, or just keeping track of what a vendor has in the present? Again, don't think of FileMaker tables, think of real-world stuff that needs to get dealt with.

 

Have you read the White Paper yet? When you download it you can also download "Database Design for FMP". It should provide some structure.

 

Finally, I'm a bit worried that my replies may be discouraging others from giving you far better practical advice than I can, especially since you're tackling very big questions and issues and - have I said this before? - a seemingly huge overall project. Some people here (and David Kachel's company, too) offer consultancy services. I'm beginning to think it would be a very good idea for you to hire someone in that capacity - I think you need someone more experienced who has an overview of what you're trying to achieve.

[dmontano]

Hi Lingo,

 

I am working on the ERD. Driving me nuts.

 

Regarding "piece" you are correct. I am specifically trying to communciate one very specific "type" of equipment. For example, I want to know every vendor in the DB that has this "type" or "model".

 

I really do not know the best way to handle "equipment". I am working on how I can explain it better, while trying to see it the way you and others have responded as such.

 

The big question - what is it all about? Is a big question to answer. I am working on plotting that to help shed some light.

 

I really want to create solution based on modules already built. For example, I understand that there will be integration work when a new solution is identified as a need. I would like to be able to do this:

 

The new solution will need:

1. Employee Data. Check - been built, plug in, integrate.

2. Vendor Data. Check - been built, plug in, integrate.

3. Languages Data. Check - been built, plug in, integrate.

4. Color Space. Do not have - build it, plug-in, integrate.

 

I am not trying to sound simplistic, just trying to illustrate the concept.

 

From all the resources I have read, it sounds like the concept is a valid one with both benefits and drawbacks. In my situation, I believe this is the way to go for me - modular. Also, many of these modules are immediately useful. For example, vendor data can be used as an island so to speak. But very powerful when integrated with "Estimating" and Job Ticketing, and Purchase Orders, etc.

 

About missing entities - you are right. I am trying to do this solo - with the help I get here.

 

I have read the White Paper. More than a couple of times. I have tried very hard to keep many of the items mentioned in the White Paper in my DBs. Some are way over my head - I am trying to learn.

 

I appreciate your advice. I have looked into consultation. I would be paying for this right out of my pocket so I am trying to cram before the exam - so to speak. I have a remote consult I am trying to set up with someone - but I can only budget an hour. So I am trying to formulate the best topic I need to focus on to get the help I need. I know whole heartedly that this is now a big project. I know I will need lots of help, be it a book, a forum, the occasional consult fee. And the hour I am going to pay for will hopefully get me over at least where I am stuck. Then I will be back stuck again....

 

Thanks,

David

[LingoJango]

I am working on the ERD. Driving me nuts.

 

Been there; heck, keep going back there! But the more clarity you have there, the clearer will the rest become.

 

I really want to create solution based on modules already built. For example, I understand that there will be integration work when a new solution is identified as a need.

 

That makes sense, but the choices you would make for a single module can be quite different from the choices you would make for the whole thing, so the clearer your overall plan the better.

 

The new solution will need:

1. Employee Data. Check - been built, plug in, integrate.

2. Vendor Data. Check - been built, plug in, integrate.

3. Languages Data. Check - been built, plug in, integrate.

4. Color Space. Do not have - build it, plug-in, integrate.

 

I'm interested in your Languages Data module. Would you mind telling me what it's about and what it does? Languages are my thing, and haven't been easy to deal with.

 

I have read the White Paper. More than a couple of times. I have tried very hard to keep many of the items mentioned in the White Paper in my DBs. Some are way over my head - I am trying to learn.

 

Do read Database Design for FMP as well. I really like the way it starts with the desired output and works "backward" from there.

 

And good luck. The way it's going, I probably won't be able to provide much help - but I'm always good for a platitude or two. smiley-wink

[dmontano]

Hi Lingo,

 

I term of my language solution - it is quite specific to a need we have. I will try to explain.

 

One of the things we create is marketing collateral - lots of it. Primarily in English - but expanding increasingly into other languages such as French, German, Spanish, Thai, Japanese, Chinese, etc.

 

So, if you can imagine have a couple hundred collateral pieces in English, then expanding languages, the number of collateral pieces multiplies substantially.

 

I am building a tool (separate from all my other discussions and posts on the forum) that will provide users the ability to have a file name "created" for them based on the criteria that I have identified as being a necessity for proper identification, organization, storage, retrieval, etc. of any such documents named with this tool I am building.

 

Therefore, just one of the elements I have identified as being critical in identifying a document is its language. So, I have to devise a code that signifies what language it represents, but the code can not conflict with any other "element" I have identified as being crucial to the documents unique identifier.

 

In a nutshell, I am hanging my hat as much as possible on ISO standards. For example, with languages, you have both a 2 digit or 3 digit code that ISO recognizes. I am using these codes to provide the foundation of my system as much as possible.

 

One thing I have learned over the years, is I may have my "own" system, but can not expect others to adopt it willingly as they may have their own code and system. Basing "a" system in recognized standards does not give one much to argue about and helps alleviate "who"s system it is. It is somewhat neutral. A Swiss approach - if you will.

 

If someone does not like the code, they can write a letter to ISO and argue their case - until then, we should use a recognized standard.

 

I do not know if this is in any way applicable to your situation - I somewhat doubt it, but if any of this touches on what you are doing - feel free to respond back and I can share more - but what I have mentioned above - is a pretty good summary if you can figure out what I am trying to say.

 

As for my situation, I am goin crazy - I took a few days off. I had worked 2 1/2 months straight without a day off on this stuff AFTER I got off of my regular job and believe I had crashed mentally. Talk again soon - I am still trying to recover, regroup, read, study, and then post another barrage of questioning.

 

Thannk you for your help and responses, I do look forward to them!

David

[LingoJango]

All praise to an American willing to embrace international standards. smiley-smile

 

If you're dealing with marketing collateral, you might want to consider using the ISO codes with a country code attached, e.g. en_US and en_UK for American and British English respectively, or at least allowing for it. Sooner or later you'll need that distinction, since one dialect's perfect jingle will eventually turn out to be another country's obscene phrase.

 

I'm relieved you're not trying to produce output in different languages yourself from the database. I am (invoices & such), and it's devilishly complicated.

 

As for my situation, I am goin crazy - I took a few days off. I had worked 2 1/2 months straight without a day off on this stuff AFTER I got off of my regular job and believe I had crashed mentally.

 

Well done. Do not reply to this message of mine just yet, and take a few days off from after-hours computing if you can. (I'm not just good for platitudes, I can dish out advice I do not take myself like there's no tomorrow; but I have a deadline.)

[dmontano]

I have attached 3 docs for my Vendors Module:

1. My revised "ERD"

2. My revised "Relationship Graph"

3. My attempt at expressing one portion of the module in laymans terms.

 

I have done my best to try and make it so it can be deciphered.

 

This thread topic may no longer make sense - I am not sure. I do not want to post this as something new - but it sure seems to have morphed due to one problem uncovering other problems.

 

I have been advised by many to get the big picture down on paper and create an ERD. This is the result.

 

I have quite a few areas that I am stuck on - so in the spirit of focusing any help I can get - can I solicit for help in this fashion:

 

1. Anything that is obviously wrong - please comment on that first.

2. Anything that screams I am doing it the hard way - please comment second.

3. If anyone can examine what I am trying to do with "assigning" any of our employees to either a particular table (as a global), or when I am trying to assign an employee of ours to one record (not global) - such as a procedural step in Vendor NDA - I would greatly appreciate it. This area is highlighted in blue in my Relationship Graph.

 

Here's what I think my problem is in item #3 above:

I have "Our" employees in a separate DB file called "Employees". Each employee record has a unique ID. Yeah.

 

Now, I want one of "Our" employees to be able to select their name to be the "Coordinator" from a drop down menu in a record using the Vendor NDA Layout (which resides in Vendors NDA Table in the Vendors DB file.

 

Next, on the same record they can select any other employee of ours to be the "Recipient".

 

I have this working. Drop-downs and all. They are using the Primary key from the "Empoyees" db, but I am not attaching them to one common "foreign ID key" in the vendor nda table. I tried and can not get it to work.

 

So, my relationship graph is ballooning because I have one TO for each instance like this. If this is the correct way - I am going to be floored. It works, but it seems like a TON of overhead to do this. That is why I thought I needed some sort of join table (not that I know how to do that).

 

Is this many to many? If so, what is the language?

 

A Employee can procure more than one Vendor NDA, a Vendor NDA is procured by many employees? Many to many?

 

Does this 100% accurately mean I need a join table? Is the join table called: Vendor NDA Team?

 

If so, do I repeat this procedure for each table that has this sort of "multiple assignments of people? I have a similar situation when it comes to the "Vendors Employees". I have more than one Vendor Employee I am identifying as who is involved in procuring the Vendor NDA from their perspective.

 

Lingo - I know you are busy - so ignore this. When you get a chance - chime in if you'd like.

 

Any help?

[dmontano]

I don't know how the dancin banana got in that post?