Help on an ERD, tables, relationships, the works.

[dmontano]

I have struggled and struggled with this - and one of the kind moderators suggested I throw this out in Portal & Relationships for help - so here goes.

 

I have attached a pdf of my "attempt" at a ERD for a vendors database. A few of the tables I think I have a understanding of - many others I do not. I have more than a few problems and questions, so I will randomly pick one "branch" to focus on and hopefully move my way through to other branches in subsequent posts. (I am being optimistic that all you forum folks will stay with me on this and get me up and running!)

 

I will pick the easiest first - hoping it will shed some light on the other issues I have.

 

Problem 1 (I think)

Our company has many employees. Any one of these employees will be "assigned" as the "publisher" of a particular table in the Vendor Database.

For example:

John Doe will be the "Publisher" of the "Vendor" Table.

Mary Sue will be the "Publisher" of the "Vendor Employees" Table.

 

If John Doe quits one day, I will "assign" another employee to be the "Publisher" of the "Vendor Table".

 

I have created the "Vendor" table "Publisher" field in the "Vendor" table, use a drop down that shows a list of our employees to choose from. That value list is coming from "Our Employees" Table from a different DB file and uses the primary key but display their name in a stacked field arrangement.

 

I am using the Primary Key from "Our Employees" table and joining it the the field I created in the paragraph just above.

 

It works, but I really suspect that I am suppose to be joining the primary key from "Our Employees" to a foreign key in "Vendor" table for the "publisher".

 

Another problem is, I will have a similar field on "Vendor" table for an "Administrator" who will be one of our employees.

 

I now have (2) different employees of ours in one record. So, I think this is a many-to-many? Do I need a join table?

 

Basically, I am finding that I want to be able to "assign" more than one of our employees to one particular "Vendor" record.

 

In this specific example, I will store both the administrator and publisher as globals. When John Doe quits and I have to reassign a new employee to be the publisher - say Mike Jones - it will be a blanket assignment for all "Vendor" records.

 

I will stop there in hopes of focusing on just that.

 

Unfortunately this is not even clearly pointed out in my ERD, so hopefully someone can do some interpretation on this. I hope to clear things up as I go.

 

Any takers on this one?

[Weetbicks]

Sounds more like a FileMaker design issue than an ER diagram issue. The ER diagram doesnt exactly translate onto the filemaker relationship graph (in a 1 to 1 fashion if you like... hehe)

 

I'm not sure about making these fields you speak of globals. Global field values are session and user specific. If you set the ID numbers of employees into the globals, then no other user will see these.

 

What exactly does a publisher of a table mean? Is this used anywhere ie in privilege sets, so the publisher is the only person who can edit the vendor table?? I don't get it.

 

If theres really no relationship between the employee & vendors that serves any functional purpose, you could create a table called "Settings" or something, make 1 record, and make fields:

 

Vendor Publisher

Vendor Administrator

 

etc... In these stick employee IDs of employees who are the publisher/vendor etc. Theyre not global so theyll persist.

 

If you need to assign more than 1 employee to a "table" you can either put multiple employee IDs in these fields, or use a join table.

 

---

 

The whole concept of assigning employees to a "table" seems odd, can you provide any more insight into this? Do you just mean assign an employee to ALL records in the table??

[Maarten Witberg]

I'm also wrestling with understanding the purpose. If it's a matter of privileges, store the privilege set name in each record and assign accounts to that privilege set according.

And protect the tables, layouts, records and fields based on the priv sets.

I can imagine a situation where you have ownership on the record level, in that case store the owner ID in the record (upon record creation). Should the current owner quit his job, then you'd need a script to reassign a new owner to the records. Always assuming that kind of operation is admin level.

[dmontano]

Thanks to each for the help and the courage to take the plunge.

 

Good questions. I will elaborate on the reason for a "Publisher" of a particular table. It has no correlation to the term "publisher" used in FileMakers privileges - just coincidence. I am using the privilege window with good success - I keep it very simple.

 

We have over 1,000 employees, many departments, many offices, many continents. I want to be able to allow any employee to be assigned / tasked / appointed as the person who will oversea a particular tables data. Kind of like a supervisor - if you will - for the table data.

 

Imagine there are 30 people who have data entry authorization in one particular table. I want the company to be able to "assign" the most appropriate one of these individuals to be "known" as the "publisher" for that set of data entry set. That allows us to:

 

1. Assign a point person. They interact daily with data entry users.

2. I can train more in-depth with this one person, who in turns trains the others as they come and go. I might have a meeting with 10 "publishers" about how the "solutions" are coming along - instead of 300 people shoved in a meeting room all chiming in about the solution they are working with to do there work - i have 10 people who are much more versed in their respective tables data.

3. Any of the 30 data entry people can see who they are to ask for help, questions, etc. Not only about the data entry process, but also about the business process for that table.

 

For example:

Any of the 30 data entry people may ask the "publisher" of the vendor employee table:

"Hey, I can not get this record to print!"

"I really dislike how this screen entry flow works!"

"Should I add this Vendor Employee to our system?"

"Should I send a new contract to this vendor?"

 

The "publisher" will collect and evaluate comments and be a centralized point to provide input, suggestions, to the solution to the "Administrator".

 

Administrators will be far fewer that publishers and will have the ability to either poll other administrators on what action should be taken, or if simple enough - make that alteration themselves.

 

This is really all about a business process management mechanism.

 

As for the Administrator role, this is the point person who can make immediate changes to the actual filemaker table for that solution.

 

In a way, I am creating and facilitating a development and management "team" of both the business processes and the development of the solution. Hopefully leveraging that in a fashion that allows the system to be maintained to be in lock-step with process and software.

 

Weetbix brings up something that has me concerned though. I was under the assumption that if I created a global field for a publisher, that the value (their name) will appear in every record in that table - until it is changed in any record.

 

Are you saying that if a user opens the solution up that there will be no value whatsoever in the global field? The only time the value would change is when we decide another individual will now take that role and responsibility.

 

I hope this sheds a little more light on what I am trying to accomplish.

 

Thanks

[Maarten Witberg]

this is what I could come up with. It uses a utility table where you store employeeIDs for admin and publisher, one record for each table you wish to assign roles to.

the advantage of this approach would be that you only need to change the admin and publisher ID in one location. and if you should decide upon another table that needs these roles, you just create a new record. It is probably smart to tie this in with your process of creating accounts and privileges, because there needs to be a match I think.

It's a bit fiddly and probably can do with some more thinking about.

[dmontano]

Hi Kjoe and Weetbix,

Thank you for the sample file. Much appreciated. You have both mentioned that another table may be in order.

 

I think what you have suggested is a good idea. I had asked previously on the forum if another table, or join table was in order - however I am sure my previous attempts at explaining what I wanted to do was far less informative than this thread I started.

 

It was advised that I try and explain what I wanted to do. That was good advice.

 

Regarding what you have suggested, I have suspected too that I may want to create another table that has accounts and privileges information WITH this type of Table Assignments.

 

I had asked that question in a different thread as well but never got a response that supported my suspicions.

 

All of this brings up another question - but certainly leading up to what hopefully will be the OPTIMAL way of handling what I am trying to accomplish.

 

I have requested help with the "Vendors Database" as my context. In reality, the Vendors Database is just one "Module" of many "Modules" I would like to develop. So, with what you have suggested, does it make sense for me to build the table you have suggested OUTSIDE of the Vendors Database file in its own separate DB that has all Accounts, Privileges, Table Assignments, etc. and then use that to tie in all of the other Modules?

 

For example, a "System Management" DB?

1. Accounts Table

2. Privileges Table

3. Table Assignments Table

4. Password (I think I have read that a password table may be a bad thing to do)

5. Other Tables that are "System Management" related??

 

I have already found a use for a module independent file (I call it "Master") that I have started to create value lists so I would not have to recreate something OR try to remember which DB file had the value list I wanted.

 

As you can see - I am really looking to build the infrastructure the best way I can in order for the solution to succeed. Given that FM has many ways to accomplish something - I am soliciting for other develops ideology as well and I try to understand their words of caution.

 

I am aslo very interested in a response to Weetbix's point about Globals in my current set-up. I am a rookie - so I am asking to learn and understand: if I keep my current set-up with globals for these two fields "Publisher" and "Administrators" are you saying that in a multi-user solution - only I will see the field values for those two fields and they will be blank for all other users?

 

And lastly, since my current set-up is functioning, is a new table as KJoe pointed out hard to integrate later into my current set-up - when I can figure out how, find the time, and build it?

 

Any further insight is HIGHLY appreciated.

 

Thanks,

David

[Maarten Witberg]

For large solutions your strategy is, to the best of my knowledge, good. A master file / table with systemwide information fits within this structure. I personally would not go so far as to create a separate privileges table though. I remember Jack Rodgers convincingly arguing for the case of an accounts table for management purposes, but I can't find the thread on short notice. Maybe he can chip in and point us to it.

Whatever you choose, the accounts and privileges feature of FM should be the place to manage your database security. The scripting tools for Accounts and privileges are quite powerfull and should help you to create an administrator tool for a non-developer level person. As I said, an additional table for accounts may be helpful for you. Just don't store any passwords there or create a login procedure with it.

 

on globals:

As Weetbix said, globals are session specific and default to their original value on session close. This means the last value that was in the global before the solution is networked, will be the default. This gives you limited control over what's in them at session start. And certainly if a user changes the global thinking he would do so for his co-workers also, he's going to be annoyed that isn't so.

 

You can use a one-record utility table in the way Weetbix suggested to circumvent this problem. Your master file would be the place to

put this. The values could be related by X-join to whatever table you need them in, or read into globals or $$variables in a startup script.

 

I don't think it would be very hard to integrate my demo in your solution, but it is always a good idea to consider your structure carefully before building or changing it. Especially if your future plans are as ambitious as yours!

 

good luck / happy filemaking

 

maarten

[dmontano]

Thanks Kjoe,

 

Your contributions are enlightening me. I think your comment on my future plans being "ambitous" is a very kind way of saying, "I have bitten off more than I can chew for my obvious skill set". I certainly can't argue that.

 

However, someone once asked me "How do you eat an elephant? The answer: "One bite at a time". Looks like I will be eating for a long time - I can not even figure out how to us a fork.

 

There are so many deficiencies I encounter at the workplace that can be greatly improved and it pains me not to be able to implement the "macro" solution in my head. Hence - I am going to do build it by hook or crook - okay not by crook.

 

So back to the globals. Since I would rarely change these values for a table - it sounds like it is not a big issue - I would have to do this at a time I know all users are out of the system - like at night, weekends, etc. But Weetbix's point shows there is a better way to avoid the asterisk approach to things like this.

 

So I think I can close this "One" portion out of my "Many" issues by summarizing as follows:

 

1. To handle Publishers and Admins as I have so far built in my current set-up is not flawed to the point of failure.

 

2. However, there is a better way, especially when I take into account the number of DB files and Tables that will be built in the bigger picture.

 

3. Make mental asterisk note about changing globals for either of these two fields on any given table. Change when users are out of system.

 

4. On my next "module", build and implement the "Accounts" table method. If time allows, integrate that "Accounts" table to the current "module" that is the context of this thread. Sounds like it would not be not-too-difficult for the average developer (something that I am not - right now).

 

5. Since access is controlled at the file level, use the proposed "Accounts" table as just a "system management" tool to help me keep things organized - like a utility to implement things like Pubs & Admin assignments, store value lists that other DBs use, (scripts?? - not sure but I hate writing them over and over).

 

If I can get a general confirmation on this, I will post the next area I could use some focused help on.

 

Thanks Kjoe and Weetbix - I know your responses take time and effort and I do appreciate that.

[Maarten Witberg]

I think I can generally confirm your points. I would however urge you to consider a few things:

1) get Filemaker Advanced first thing in the morning. It will save you huge amounts of time developing. I would go so far as to refuse to do any serious developing (and that means anything with more than two tables and a script ) without it.

2) It kind of surprises me that you seem to be burdened with developing a business critical solution for a company of 1,000 employees while you describe yourself as a novice. From your posts I gather you are a thoughtful and smart person, and it looks like you are above novice level, but if I was your employer I'd readily let me be convinced that a) you need to follow some training and b) be allowed access to professional help either as a coach or as a (co-) developer. They are going to save money later on by investing in you now.

[dmontano]

Kind words - thanks. I find the IT and business management community have a perception that most anything developed outside of an IT or MIS department is not worth doing separate. I am sure - generally - that may be the case.

 

However, FileMaker is a unique creature - I believe. It allows those who do not have any formal "programming" to get involved on their own. We can build little things here and there and at some point we start thinking, reading, wondering if this tool could be used for larger solutions. The light bulb goes off in the FM "developers" head and they start to recognized all of the processes that are being done in such arcane methods that we start to scratch our head and wonder how come IT doesn't build this and that.

 

This is where I think FileMaker has a true niche that should be exploited. Many companies have big systems - as we do. However, the company is big as well. I think there are many IT departments that primarily exist to keep things running - not so much developing solutions for one particular department. If they were soliciting each department for software solutions we want - I would not be doing what I am doing right now.

 

This is not my job function. I basically work in Marketing and Advertising, however I have a deep level of experience in the printing and graphic design industry. I help other designers to keep things moving as smoothly as possible with creative, design, and print related projects and do all significant overseas press-checks myself.

 

In essence - having owned a business for 13 years previously - I see things a little differently than those I now work next to. This is not a slight on them - just a mere result of owning one's business the mindset has to be different.

 

I look at it this way. I am doing this solo where I work at, and hopefully as I finish a couple of these "modules" where they are actually fully functioning in of themselves, I can illustrate the macro issues such as security, deployment, integration, and show how additional business processes that are currently neglected can be brought into the fold. I hope to offer significant value for the company for my efforts - that is my goal.

 

There is a saying, "Walk softly, but carry a big stick". This aptly applies in my situation. If one screams and hollers we need a database solution the opening observation I made in the first paragraph rings true. "This is an IT item, and we don't use FileMaker. IT has bigger fish to fry.

 

I would present it as such: here is a solution to some long-time standing inefficiencies we have around here. See how this solution works, see how I have integrated it, see how easy it is to facilitate this process?

 

When a solution is built and functioning - it would be quite odd to hear anyone say, "No, lets continue to do each RFQ in a separate Excel doc, MS Word Doc, and lets continue to have no accounting or search retreival of these docs and business processes.

 

Your advice is spot on. I will have to go this alone until I have 2 or 3 fully functioning modules. I would be shooting myself in the foot if I let the cat out of the bag prematurely. I foot the bill personally for anything related to my development of what is in my head, so right now, my daughter still needs those braces and I will have to suffer in 8.5 Pro instead of Advanced.

 

This will be a long row to hoe - but here I go.

 

I will be posting additional requests in this thread to keep the picture in focus. Your help is greatly appreciated - but never expected - so many thanks!

 

David

[Ender]

Hey dmontano,

 

You know kjoe and Weetbix know their stuff, so no argument from me. Although they've covered some of this, I'll try to lend my perspective.

 

I would caution you to put additional thought into the security schema (that seems to be what we're really talking about here). Particularly about assigning roles to different employees for various tables.

 

In my experience, you will likely see a variety of access requirements for different tables. And these requirements may change as people are hired or leave, as someone fills in for the vacationing executive, or at the whim of management. It is therefore useful to have some flexiblity with the security schema, and allow for quick, easy changes.

 

FileMaker's built-in Accounts and Privileges is a great foundation for this. There's fine granular control that's possible as it is, and you can always extend it to have scripts check the privilege set or account name before allowing some action. The addition of a User or Account table is mostly useful for relationship and UI purposes, but can also be used to extend the privileges of users with multiple roles.

 

I know you were thinking that a single "Publisher" would be assigned to each table, but I think this has some problems:

 

1. Somewhere down the road, you may be asked to add an additional user as a Publisher for a table. If you're using a field to assign the Publisher, it would require a change to the structure to allow two Publishers to be assigned.

2. Each module within a large solution is likely to consist of several tables. Some are simple back-end tables, like Phone Numbers or Addresses for the table they connect to. Having to add the same Publisher in each of these minor tables (a Publisher that is presumably the same as the parent table), seems redundant.

 

In my opinion, you should start with the built-in privilege sets, maybe define each to be a basic role ("Human Resources", "Sales", "Sales Manager", "Marketing", "Volunteer", or whatever). And if you have people that act in different roles (maybe do things with data in different departments), use a Role table and a User-Role join table, so you can have multiple Roles assigned to each User. You can then look at the current privilege set or the current user's Roles to decide whether to allow the user access to different areas.

 

Instead of assigning Publishers for each table, assign them as privilege sets or Roles for whole modules. For example, the "HR Publisher" would be in charge of all the tables in the HR module.

 

By managing roles like this, it would be pretty easy to reassign employees to other (or additional) roles, while the system is live.

[dmontano]

TOPIC NUMBER 2 (This is harder for me)

Goal: Managing and Using Vendor Equipment data to help search for the right vendor based on type of equipment a project requires. There are other uses - but this one should suffice for now.

 

Context is my Vendors Database.

This one is much harder for my to "see" how the structure of the database should be from the aspect of tables to create, layouts to create to allow data entry, portals to create and where to allow viewing, etc.

 

So I will start out in baby steps for my sake.

 

In my earlier posted ERD (attached in previous post) I have modified my thoughts to have 3 tables for this "branch" of the solution instead of 4 tables. New table structure is:

1. Vendor Companies

2. Vendor Equipment Association (like line items??)

3. Equipment

 

Focusing only on the Equipment table, I am going to follow the advice of what I have been given in other threads and create one "Equipment" table instead of one table per Equipment Type.

 

For example, instead of 1 table for Printing Equipment, 1 table for Bindery Equipment, 1 table for Computer Equipment, etc. I am going with 1 Table for all Equipment.

 

It will hold 1 record for every conceivable type of Equipment any vendor has or can have. For example, imagine I have records of 1,000 vendors:

 

350 of these vendors have any quantity of a:

1996, 2-door, ford, focus, car.

(I will only have one entry in my equipment table for this type of "Equipment".)

 

27 of these vendors have any quantity of a:

2003, 8-color, Heidelberg, Speedmaster, Printing Press

(I will only have one entry in my equipment table for this type of "Equipment".)

 

294 of these vendors have any quantity of a:

Apple, Mac, G5 Workstation

(I will only have one entry in my equipment table for this type of "Equipment".)

 

Now imagine, I have a "Vendor Profile" layout that just shows all the details of one vendor. I have a 7 bank "tab" on my "Vendor Profile" layout that is based on my "Vendor Companies" table. 1 of these tabs is called "Equipment".

 

I want a portal in this tab that shows this vendors equipment only - including how many of one particular type of Equipment they have.

 

I believe my revised table structure mentioned above that has the "Vendor Equipment Association" table will provide this data association structure for me and allow creation of a portal to "Vendor Equipment Association" showing equipment related only to that particular vendor.

 

So the result for this sample vendor: Worldwide International Company

would look like this in their portal:

3, 1996, 2-door, ford, focus, car

2, 8-color, Heidelberg, Speedmaster, Printing Press

12, Apple, Mac, G5 Workstation

 

and,

 

The result for a different vendor: ABC Company

would look like this in their portal:

4, 1999, 4-door, chevy, malibu, car

1, 2-color, Komori, Lithrone, Printing Press

3, Dell, Inspirion, Workstation

 

(Notice quantity value at the beginning of the portal row. ABC Company has "3" Dell Inspirion Workstations)

 

If anyone can confirm or deny that so far this is structurally sound and correct, it will allow me expand in my next post to where I have more questions and problems.

 

If it is structured wrong here, it will be wrong later.

 

Thanks again,

David

[dmontano]

Hi Ender, I am going to back up here to the first topic in my post, I must have been typing while you had already sent the post.

 

Thanks for your reply. I can not tell all who are lending a hand how much this is helping me think.

 

I have summarized for myself - based on your comments - that I need to look at Accounts and Privileges to do what I am trying to do. You mentioned that this is really about security schema. At first I thought not. Then I started thinking if I can tie the "business process" to the security - it would be fundamentally the same. I am a little gray in my thinking right now - I am going back and forth in my head.

 

Here is what I "get" currently from my setup:

Every record layout "shows" who the "publisher" is, and who the "administrator" is. Next to their fields showing their names, are their respective email fields that have an email script that a user can select and send them an email - like this:

 

"Hey Joe Publisher, I can not secure a contract with this vendor. What should I do?"

 

or

 

"Hey Joe Publisher, this window has a button that doesn't work."

 

I am trying to facilitate creating an environment where inter- and extraoffice communications is very easy.

 

To take it one step further, who ever is the publisher of lets say the Vendor Company table - may be completely outside of their expertise to be the point person for other employees to look to for much more complex vendor equipment data.

 

I think you are saying I can control this through accounts and privileges - I agree. When I thought I wanted "x" functionality I will build these pub and admin fields and include their emails and make them global so I can change all instances, etc.

 

I do not like the "overhead" this creates for me though. You are absolutely correct that these Admins and Publishers will have to be properly accounted for and set up with respect to permissions - so I can clearly see the corollary.

 

However, let's say I do not execute as I have already set up. Let's say I go the Accounts and Privileges route. How do I get a few fields of their data on each record? Full Name and Email. I have them now, I do not knwo how to get them back on my layouts if I go the other way.

 

The more I think about it - this would be perfect. Previous way, I would have to remember to change not only each Tables global - which Weetbix pointed out is a little flawed - but I had to alter Accounts and Privileges as well. I thought that I was just going to have to do that.

 

Now I am very interested in how to do that - if it is possible?

[Ender]

As I mentoned, a User-Role table would be needed for relationships and UI. Your Publisher Name and Email are perfect examples of where this would be needed.

 

So the Accounts and Privileges would enforce whether someone has overall access to data in each table. And the User-Role table would be used to see the Name and Email on each layout.

 

For this relationship, you'd add a globally stored text field (or unstored calc) to hold the Table Name (or Module Name). This would then be related to a new TO of User-Role by that Table Name. Add another TO of the User table linked to the new User-Role TO, and you can then see who the people are that have roles with that table. Add another criteria to that relationship to further filter it by the specific Role you're interested in, in this case "Publisher".

 

Although it's a little more work setting up additional TOs and relationships at the start, it should be easier to manage in the long run.

 

See attached.

[Maarten Witberg]

There is a saying, "Walk softly, but carry a big stick". This aptly applies in my situation. If one screams and hollers we need a database solution the opening observation I made in the first paragraph rings true. "This is an IT item, and we don't use FileMaker. IT has bigger fish to fry.

Fair enough - your 'filemaker niche' is a good one. It sometimes strikes me that IT departments seem to think that the company is there for them and not the other way around. Nevertheless in the end you need them to roll out your solution. And you need support from your superiors to work on a demo and proposal to change business processes. You say you need to walk this alone in the first stretch and while I think you may be right in that you need to do your research before you make your pitch - I also think that if you have no support from the start, your solution will go nowhere no matter how brilliant it is.

In my view you need to consider your situation very hard if you even have to hesitate about asking your superiors pay a few hundred dollars to get you one copy of a tool you need to make this demo.

What is your rollout strategy if you have no filemaker installed base in a company of 1,000? You're going to need server advanced; you're either going to need 1,000 copies of filemaker or a good web-based solution which will require additional skills and software - as IWP won't do. And you need very good dedicated hardware to make things run smoothly. IOW this is big bucks stuff even without the cost of development considered, even when you do it all for free in your spare time. And even if only 10% of the company's employees are going to use your stuff. All that said, a good solution will save money.

 

As an aside, you need to consider the limits of filemaker server, which supports up to 250 simultaneous users (that is, sessions running at the same time). How many of the 1,000 are going to use your solution concurrently?

 

When a solution is built and functioning - it would be quite odd to hear anyone say, "No, lets continue to do each RFQ in a separate Excel doc, MS Word Doc, and lets continue to have no accounting or search retreival of these docs and business processes.

I think you need to separate two issues - one is the beauty of filemaker as a development tool and the possibilities it gives to the non-IT world, and the other is what is good for your company. My guess is you have got good ideas about business processes in your organisation. Tackling them with filemaker may be a good idea, but you need to tackle them with your colleagues first. The choice of technology involved comes second.

 

I hope you do not find me presumptious saying all this. Like you I am approaching database development from the other end. And I am not a coach nor a BA expert. Still I hope this helps you organise your thoughts.

 

 

EDITED in red

[dmontano]

Hi Ender,

 

Thanks a bunch for the samples and your time - sincerely. Unfortunately I do not grasp the concept yet (more evidence of my inability to see what comes easy to others.) I am studying it and trying to modify the language of the tables and field names so I can see the context. I am hoping that very soon something clicks for me and it will become easier. I can almost hear you sigh! I do not expect you to walk me through - I can imagine you have done this a thousand times.

 

Hi Kjoe,

Very valid points and recommendations - thank you. Maybe I painted the Co. that I work for incorrectly - my superiors are aware that I am working on developing some solutions for our department specifically. They have honored my request for 4 copies of FM. (I am just "hearing" from the likes of you how FM Advanced is so critical). I have one copy of FM Pro and have provided 3 other individ. with FM Pro copies as well. This is the "team" for now. I am the only one doing any programming though. This "team" is really a highly specialize group of people that are tasked with creating and managing certain assets for the company as a whole, because the asset is our speciality.

 

In addition, I share my procedural experience with other people in the department and knew that FM would be a great way for me to document, share, and to build highly specialized tools for particular mini-groups to control data into the server.

 

For example, file naming & location are the cornerstone to a successful Digital Asset Management system in order to leverage other software that have built-in features that pull file name and folder heirarchy into the metadata stream - saving all from ever thinking about metadata. Most big systems for sale delve deeply into metadata tagging and I have found a structural way to eliminate manual tagging- while maintaining strict control of the nomenclature and to run in parallel with security schema of the server (Sound familiar with the advice you and others have mentioned in this thread?)

 

Telling people how to name and where to put a digital asset is guaranteed failure due to human nature. No one person can remember the nomenclature of any particualr asset and hence - will be named slightly different and filed slightly different each time (very similar concern to field validation in FM). The parallels are striking - to me.

 

One HUGE tool I am building is a file naming and file deposit (or locator) tool with FM. The knowledge is built in to the tool by the programming and executed through simple drop down lists. It generates a name and generates a UNC path. All based on Mac and PC file conventions and limitations taken into account to ensure open architecture and flexibility with integrating practically ANY software solution that pops up.

 

As you can see, this is quite separate from the help I am asking for now - Vendors Database. However, I have found that one tool soon sheds light on another tool that would be "GREAT", therein lies the rub, I find myself building another tool and then it spawns another tool. I see many holes I think I can plug with what FM can do - coupled with what I know how to do. I have went down the road far enough to take a huge step back and realize that I need to learn a lot more before I paint myself in a corner.

 

This forum - and responses - have helped immensely - I find my progress slowing to an absolute crawl because I am heeding many warnings, recommendations, advice, etc. It is causing me to think and try to learn as much as I can about first - "data modeling - ERDs". Sounds like that is primary. Next to learn FM at a much higher level than what I have been doing up until this point.

 

So, with all of that said - I assure you that I am listening. My frustration is I do not get the core picture yet. I can see easily there is a great deal of experience amongst those here in the forum - and I post, read and visit as evidence to my belief.

 

If I can get any of you to hang in there long enough - I may be able to get over this huge hump that has me stumped. I am the weakpoint - but really working hard at it.

 

At this rate - with all my posts - you all may get to know more about the Frankenstein I am constructing, and "Frankly" that may bode well for me.

 

By the way, all comments are not forgotten. I DO go back over all of my subscribed threads because often times with the advice given at the time - it is beyond my comprehension or abilities. So I come back later and sometimes little things click and I can solve one of the myriad puzzle pieces. (Insert my attempt to persuade you all to keep the recommendations coming).

[dmontano]

Not trying to double-post, just trying to ask if I can focus on Post# 12.

 

I call it Topic # 2.

 

Could I get any feedback? A confirmation that what I am trying to do with "Equipment" so far sounds correct? Incorrect?

 

Thanks

[Ender]

TOPIC NUMBER 2 (This is harder for me)

Goal: Managing and Using Vendor Equipment data to help search for the right vendor based on type of equipment a project requires. There are other uses - but this one should suffice for now.

 

Context is my Vendors Database.

This one is much harder for my to "see" how the structure of the database should be from the aspect of tables to create, layouts to create to allow data entry, portals to create and where to allow viewing, etc.

 

So I will start out in baby steps for my sake.

 

In my earlier posted ERD (attached in previous post) I have modified my thoughts to have 3 tables for this "branch" of the solution instead of 4 tables. New table structure is:

1. Vendor Companies

2. Vendor Equipment Association (like line items??)

3. Equipment

 

Focusing only on the Equipment table, I am going to follow the advice of what I have been given in other threads and create one "Equipment" table instead of one table per Equipment Type.

 

For example, instead of 1 table for Printing Equipment, 1 table for Bindery Equipment, 1 table for Computer Equipment, etc. I am going with 1 Table for all Equipment.

 

It will hold 1 record for every conceivable type of Equipment any vendor has or can have. For example, imagine I have records of 1,000 vendors:

 

350 of these vendors have any quantity of a:

1996, 2-door, ford, focus, car.

(I will only have one entry in my equipment table for this type of "Equipment".)

 

27 of these vendors have any quantity of a:

2003, 8-color, Heidelberg, Speedmaster, Printing Press

(I will only have one entry in my equipment table for this type of "Equipment".)

 

294 of these vendors have any quantity of a:

Apple, Mac, G5 Workstation

(I will only have one entry in my equipment table for this type of "Equipment".)

 

Now imagine, I have a "Vendor Profile" layout that just shows all the details of one vendor. I have a 7 bank "tab" on my "Vendor Profile" layout that is based on my "Vendor Companies" table. 1 of these tabs is called "Equipment".

 

I want a portal in this tab that shows this vendors equipment only - including how many of one particular type of Equipment they have.

 

I believe my revised table structure mentioned above that has the "Vendor Equipment Association" table will provide this data association structure for me and allow creation of a portal to "Vendor Equipment Association" showing equipment related only to that particular vendor.

 

So the result for this sample vendor: Worldwide International Company

would look like this in their portal:

3, 1996, 2-door, ford, focus, car

2, 8-color, Heidelberg, Speedmaster, Printing Press

12, Apple, Mac, G5 Workstation

 

and,

 

The result for a different vendor: ABC Company

would look like this in their portal:

4, 1999, 4-door, chevy, malibu, car

1, 2-color, Komori, Lithrone, Printing Press

3, Dell, Inspirion, Workstation

 

(Notice quantity value at the beginning of the portal row. ABC Company has "3" Dell Inspirion Workstations)

 

If anyone can confirm or deny that so far this is structurally sound and correct, it will allow me expand in my next post to where I have more questions and problems.

 

If it is structured wrong here, it will be wrong later.

 

Thanks again,

David

 

Are you sure it's not important to know details about each piece of Equipment?

 

With our fixed asset inventory, the individual item's details are very important. We track where each vehicle and computer is assigned, when and from where it was purchased, details about its repair history, etc. The aggregate information, like the number at each building, is then a simple aggregate calc or summary count.

[dmontano]

Hi Ender,

 

I am so glad you have asked that question as that is what I was leading up to - among some other items.

 

I had posed this question before, but may have been stating it poorly as well. I was somewhat steered away from using separate tables for each type of vendor. I am not even sure if this is related to what you have mentioned, but your question is definetely going in the direction I need to go.

 

I envisioned (perhaps incorrectly) that I would want a separate table for each type of vendor equipement. For example:

 

EQUIPMENT RESOURCE DETAIL TABLES

Table 1: Printing Presses

Table 2: Bindery Equipment

Table 3: Proofing Equipment

Table 4: Raster Image Processors

 

Then tie those tables into another table that has drop downs:

 

Table 6: Vendor Equipment

Here I can assign to one vendor, each piece of equipment that I have in my equipment resource tables above. They may have 2 of these, 1 of those, 7 of these, etc.

 

Table 7: Vendor Company

Here i would have a portal called equipment that shows all of that vendors equipment "List" if you will.

 

I also want to build a "HYPER" finder somewhere that allows a user to one day say to themselves: "I need to find every vendor that has an:

 

8 color printing press

or

a GBC Binding machine

 

I am positive that I do not care about any serial numbers of the equipment as it is equipment that we do not sell, purchase, or do any service to.

 

This really is a "google" of sorts for users to find what vendor has what "equipment" and which "vendor" can considered when we make a choice to submit a Request for Quote to.

 

We may submit 3 RFQs to vendors who have the "Equipment" to do the job.

 

I hope that clarifies things more so.

[Ender]

Well, I wasn't suggesting changing your structure to have multiple similar tables. It's generally better to keep similar data together, distinguished by a Type field or something.

 

Though I can't envision what your business process is that requires only the counts of these different equipment categories, if that's really all you need, then using a single Vendor-Equipment table to hold them is an easy choice. From what I see, the basic fields you need are: Equipment Type, Equipment Description, VendorID, and Quantity. You can then add a Sub-summary report (or Equipment table with aggregate calcs) to see the counts by Equipment Description.

 

The "Hyper finder" could take a little trial and error. I don't think you could do that directly from the Vendor table. I think you'd have to do the Find from the Vendor-Equipment table, then use a Sub-summary by Vendor report, or maybe a GTRR[Match All] to jump to the related Vendors.

[dmontano]

Ender, I have been crossing my fingers hoping someone can stay with me long enough on this topic. Thanks for your contributions.

 

By the way, forget about the "Hyper Finder" that will just get in the way at this point and I have a feeling I can figure something out - or at least post a question when I get to that stage. I did mention it in case that would put a whole new spin on this "branch" of the Vendors database I am trying to build.

 

Okay, so once again I let my earlier assumption that I should be creating separate dedicated tables to hold information about a "type" of equipment get in the way of what I really should be doing - 1 equipment table. Even though the "equipment" can be very different from one type to the other - 1 table.

 

So, can you reconfirm Table Structure so I can keep my head straight?:

1. Vendor Company Table

2. Vendor Equipment Table (Use this to assign a vendor to a type of equipment)

3. Equipment Table

 

I am having a hard time envisioning a portal on the vendor layout where I can make fields appear that can hold such disparate data from one equipment type to another. Not that more than one table will solve this - I am just having a hard time with the concept.

 

For example, imagine the "vendor profile" layout with an "equipment" portal like this:

2, 8-color, Heidelberg, Speedmaster, Printing Press

12, Apple, Mac, G5 Workstation

 

To create this portal, I would have to place 5 fields from the equipment table on the portal. Since those fields will be displaying the field data for each record that the vendor has, how do I get the fields to be common?

 

This is really hard to explain...

 

Let me back up and start with data entry for a printing press in my Equipment Table:

First, a separate layout based on Equipment Table that says "Printing Presses"

Equipment Type: Printing Press

Equipment Subtype: Sheetfed

Manufacturer: Heidelberg

Model: Speedmaster

# of Ink Towers: 6

Extended Delivery: Yes

Coating Unit: Yes

Coating Type: UV

Max Sheet width: 40"

Max Sheet Length: 28"

 

Now, for data entry on another type of equipment like, "Bindery Equipment" in my Equipment Table:

Another separate layout that says "Bindery Equipment"

Equipment Type: Perfect Binding

Manufacturer: Wollenberg

Model: VARIO 7000

Clamps: 20 ((((Notice field label is different than "Printing Press" record above)))

# of Pockets: 18 ((((Notice field label is different than "Printing Press" record above)))

Gatherer: Inline ((((Notice field label is different than "Printing Press" record above)))

3 Knife Trimmer: Yes ((((Notice field label is different than "Printing Press" record above)))

Trimmer Inline: No ((((Notice field label is different than "Printing Press" record above)))

 

Notice how the fields labels must be different otherwise no one would know how to enter the appropriate data on the specific layout in the equipment table.

 

It has been suggested to concatenate what fields I want to show and have that concatenated field show on the portal. If I do so, I would not be able to search within the portal based on one field. Maybe that is just the way it is - and I need to address finding via another method. Perhaps search via the portal field is not good practice in this case??

 

If I concatenate, I would have to have one concatenating field for each equipment type to assemble the field data for one type of equipment, then do the same thing for a different type of equipment. Then create one master field that concatenates all those values in? Or do I create one master concatenating field and add all possible fields in the equipment data entry field candidates? I will have many empty fields because I am using one table for all equipment. what about all the "spaces" I have to place " " in the calc so fields are not running together in the portal row.

 

Can I get a push in the right direction based on this scenario?

[LingoJango]

Hi, David, hope you don't mind my butting in again.

 

I know very little about your business but I wonder whether some of the info you'll need to track should actually go in VendorEquipment; e.g., the inline gatherer might be an option in the Wollenberg VARIO 7000 so you need to note whether that particular vendor has that particular option, rather than the machine itself. Sorry if I asked this before.

[Ender]

dmontano, where did all that detail information come from?? I thought all you needed was the counts? What do you need the detail information for? Is it the same information for all 350 or whatever items that are being counted?

[dmontano]

(Preface: I have changed my ERD based on input here at the forum and also my terminology in hopes of making more sense to me and others. I may still be "wrong" but I am modifying as I go. Therefore, table names have changed to:

 

1. vendors

2. vendor_equipment_association

3. equipment

 

Hi Lingo,

Please do comment - the more - the better. I am not sure I follow. I may have improperly rerence the wrong source table in my previous post. Does this clear it up: The portal would be pulling data from the "vendor_equipment_association" table?

 

Hi Ender,

Thanks for staying with me on this.

 

I was not trying to "hide" equipment details, I think I was just showing what would appear on the portal in a Vendors Profile Layout. Since all of the equipmet details would never fit on a portal row that resides in a 8.5" wide page layout, I would have to "cull" the fields that I choose to fit and to show "enough" information to allow a user to get a basic idea of what the equipment is.

 

One problem that I can not see a solution for is this:

 

If I create one large equipment table (per everyone's suggestion) that holds detailed (lots of fields) information about one equipment item - I am going to end up with a couple of hundred fields - no doubt per record and many of them empty. This is based on the assumption that I throw all of these different "types" of equipments in the one table.

 

It has been said by one poster, "fields are cheap". I understand that concept. However, with a large table with hundreds of fields, I will be subjecting myself to build many layouts based on an equipment "type" to hold the field labels that must be there to properly prompt the user on what attribute is required in that field. That is not insurmountable, but there are side effects to doing just that, such as: when I create a GTRR script in a portal that shows ALL of one vendors equipment, I have to figure out which layout holds that "type" of equipment data in order to take the User to the layout for that specific equipment "type". This functionality would have to change in any portal row based on the "type" of equipment that is in the row.

 

Second, I do not see any reasonable way to get the equipment in a portal row unless I concatanate a groups of fields that are chosen by me based on an equipment type, and then I would have to assign a field to display in a portal row that looks at all of the concatenated fields for each equipment type.

 

That seems like a lot of work - if even possible. As soon as something seems like it is a tremendous amount of work, that tells me it becomes less managaeble and I am probably doing something wrong.

 

I am currently lingering on data normalization topics and trying to see if that is what I am lacking - but that topic is not easy to understand.

 

Keep in mind, I may have 20, 30, 40 "types" of equipment. Each category would have distinctly different field lables to hold their attributes, and I will go probably anywhere from 10 to 3-, 40 fields deep for any one type of equipment.

 

I have thought about dynamic field labels that change based on equipment type selected, but I have no idea how to do this and I am looking for the right way to do this.

 

Thanks,

David

[Ender]

I think it will be helpful if you can define more clearly what specificity of data you need to remember, and what the workflow is that you need for viewing this data. At this point, some things seem contradictory.

 

At first you seemed to be against having very granular information about your Equipment (not needing data about individual pieces of equipment). You mentioned a portal of counts for a few different types. In my mind that means you just want a table of Vendor-Equipment with a manually entered Count field.

 

Then in these last couple posts, you're talking about lots of details about a wide variety of Equipment.

 

Until we get the requirement hammered out, it's hard to advise on the structure.

[LingoJango]

Hi Lingo,

Please do comment - the more - the better. I am not sure I follow. I may have improperly rerence the wrong source table in my previous post. Does this clear it up: The portal would be pulling data from the "vendor_equipment_association" table?

 

Ender pretty much made my point. The issue is not which table you choose for a portal, but which table stores which data (portals can display data from any related table, not just the referenced one; whether it's meaningful is a different matter).

 

At some point you'll have to figure out all the attributes you'll need to keep track of and where they belong, and the data modeling stage is the time to do it.

 

An example using desktop printers (my league). If you were keeping track of a vendor's desktop printers to know who can print a duplex print job, a few years ago I would have decided that duplex printing was an attribute of the printer model: HP Officejet XXXX has no duplex printing, HP Laserjet YYYY does. Thus, I would have put it in the Equipment table, no sweat. Info from Equipment can be displayed in a vendor_equipment_association table, no sweat.

 

Then I learned that some printers can have duplex printing as an option. To my mind, as soon as that option applies to my company, duplex printing gets shunted to the join table (vendor_equipment_association).

 

If you can list at least three or four equipment types, as disparate as they can be, indicating *all* the attributes you'd need to track and whether those attributes are model-specific or individual-piece-of-equipment-specific, we'd have a clearer picture of what is called for.

 

Maybe throw in a draft layout design, done in Photoshop or whatever program you use for designing. Imagine how you would display the stuff and how data entry would happen for the individual company. Throw in several disparate types of equipment.

 

I suspect you'll need lots of different layouts for data entry anyway - or just perhaps just one, with a huge number of tab objects. You're not getting out of that one. :-)

[dmontano]

I can understand how this can be confusing - that is far from my intent, but a result of my knowledge.

 

I will build an excel file that shows just a "sampling" of a "few" equipment types and their corresponding fields.

 

I hope that might help. I have never done an ERD attempt until a few weeks ago - so I am very new in trying to communicate my envisioned solution. Maybe an excel doc of my table in question would help.

[dmontano]

I have attached a PDF that shows just a sampling of equipment table - does this help convey my use of equipment?

[dmontano]

Hi Lingo,

 

I must have hit the reply button just after you did and have not noticed any new "posts" in this thread up until today.

 

So, I somewhat read your mind and did create a sample table in Excel and posted a PDF of that. Not as in-depth as you requested, but maybe enough to show that I do have equipment that is quite disparate. In many case all they share is the fact that someone could say it is equipment.

 

I am making this a BIG Deal for two reasons:

1. I lack the knowledge to clearly see what I should be doing with the structure of the database. So, I am more concerned about "seeing" what the likes of you and Ender "see" when you are confronted with these issues.

 

2. I have been convinced by the kind folks here (and my previous solutions that ended in roadblocks) that time spent in planning is paramount.

 

I am at a big fork in the road and I am choosing to get this nailed down before I move forward, or else I may be going backwards.

 

Ah, the irony...

 

Let me know if my posted PDF is still to shallow in information.

[LingoJango]

Hi, David.

 

Don't compare me to Ender - I've just designed one crummy database from scratch, tinkered with a big one designed by someone else, and almost finished building one based on a commercial template. The quality of our respective advice is necessarily determined by this fact.

 

I'm a bit lost where you say "All white and light blue fields will originate in the "Equipment" record in Equipment Table.

Areas in light blue would have to be concatenated in order to be used to show in a portal row on one specific vendors profile layout."

 

Are you really saying that all the white and light blue fields are equipment-specific and not vendor-specific?

 

Portal row concatenation - if you use separate fields for each type of equipment in a single table, you can, of course, overlay those fields on each other - as long as they're transparent you should be okay. Or you can have a concatenated text field as shown in your Excel file.

 

Searching, however, is trickier. But with so many equipment types, it seems to me you might do well with a wizard-like search interface - using one tab panel for each type, for instance. That ought to make things simpler and enable you to use number fields where a numerical comparison may be necessary (e.g., if you want to be able to look for someone with printing equipment that has a maximum paper width of at least 20" - though mind you, that can make data entry tricky because of all those non-metric units, perhaps even requiring an extra calc field).

 

Where searching is not going to be quantitative just use text fields.

 

The more I look at what you're showing the more I would go for a single Equipment Types table with all the fields you need in it, and a smallish join table to Vendors. I'm sorry if I sound skeptical when you say all the info you want to track about equipment is specific to the combined equipment model, manufacturer and year - I live in a world of exceptions and options and special features, and can't help seeing them over every horizon. But it really is a crucial distinction and the cutoff points need to be pretty clear when you start to build.

 

Ender, expert advice needed. And I have to be off.