Relationships-ERD

[Mike J]

Good Morning everyone.

New to the forum, sent here by JMO. He mentioned that this site is a great tool to assist in basic and difficult questions related to FMP. I developed one or two databases, but missed the whole concept of Relational database (didn't have enough coffee and skipped the chapter).

Anyway, went back and began making an ERD and came up with a problem I'm have trouble finding the right relationships.

My DB concept is in a laboratory setting. Scientist need to order things from vendors, in turn I create Requisitions and submit them to the vendor. I want the users only to see certain things i.e. their orders, current orders (everyone's) and a receiving page so they can let me know when they got the items. I also want to create an internal catalogue so they don't have to reference a vendors catalogue or search our past requisitions for the item they ordered. The internal catalogue would be a list of all items ordered and they would be unique, no duplicates.

I created a couple entities (not a complete ERD because it just got to complicated and I'm still learning)

Line Items (Orders)

Users

Vendor

Catalogue

Requisitions

My problem has become: When I try to relate Vendors with line items or Requisitions, I have problems. I have tried to really think about the process but I get stuck on the following and how to relate them.

I submit a requistion with many line items to a vendor. I can submit many requisition/day, but they are specific to the vendors.

I have a catalogue that will be used to generate the line items (if not in catalogue then I have globals so the user can add new data to it).

I have many vendors that have many line items, and each line item will only have one vendor.

I submit a requisition to many vendors, but each requisition is specific to one vendor.

Thoughts, help please. Basic file attached

Ordering.2.zip

[comment]

The workflow you describe is not entirely clear. Suppose I am a user and you are the administrator. I want you to purchase some pencils for me. Should I choose a pencil from a specific vendor, or should I just specify '50 #2 pencils'?

Assuming user is the one specifying the vendor for each item, I think the ERD would look something like the attached. Note that users can submit requests for multiple items, so we need a Requests entity.

Each request can contain items from a number of vendors, so the administrator will have to sort unordered items by Vendor, and create an Order for each group. This is going to be the "interesting" part of the problem, because the order's line items are created before the order.

[Mike J]

The workflow you describe is not entirely clear. Suppose I am a user and you are the administrator. I want you to purchase some pencils for me. Should I choose a pencil from a specific vendor, or should I just specify '50 #2 pencils'?

Correct!! First the user goes and searches our catalogue. If they can't find what they need, then they go to a "non-catalogue" type request (eventually adds it to the catalogue).

I will reply to the PNG file tomorrow-it's late in the UK right now and I need to get home.

MJ

[Mike J]

I've reviewed the PNG. The vendorID entity, would that be another table occurence?

[comment]

Ummm... no and yes:

No, there's no VendorID entity. The entities are the rectangles. The parallelograms are relationships, and they are labeled to show the match fields.

Yes, in the actual implementation, you will need two occurences of the Vendors table.

[Mike J]

So in an ERD, should you be concerned about circular relations?

Doesn't that just suggest that there might be common link, allowing for linear flow?

[Mike J]

Just was reviewing my documents and came up with a thought about relational db.

I see a lot of unique ID statements in many posts. It seems that if you put many unique identifiers on something then you can track it.

So when is to many and when is not enough? Obviously question dependent, but as a general theory to bounce off those who've been developing for years.

[comment]

No to the first question. I'm afraid you lost me with the second question.

An ERD is just a sketch of the basic concept. It bears only a vague resemblance to what the RG (relationships graph) in Filemaker will look like - or none whatsoever, if you decide to go with the anchor-buoy model, for example.

[Mike J]

I noticed that method. A little over my head at the moment. But thanks for confirming that these are just guides. I know that, but from failing the first time in my understanding relational DB then I want to make sure I'm headed in the right direction.

[Mike J]

Updated file using the relationship you suggested. Is this correct?

MJ

Thanks for everything comment!!!

Ordering.2.zip

[comment]

I have only looked at the RG. The relationship between Requests and Line Items doesn't seem right: Each request can have many line items, not the other way round. Therefore, a line item needs a foreign key for its parent request, and there's no need for a LineItemID field in Requests.

Also, the Catalogue table needs to be related to Line Items, not to Requests. Each line item of a request is a separate choice from the catalogue, so the foreign key for catalogue needs to reside in the Line Items table.

[Mike J]

I'll drink some coffee and populate my work-in progress DB and try out the flow of work, back soon with workable data probably with more questions.

Anyone else want to comment on the flow diagrams presented (real world examples and alternative solutions to what seems to be a very common task).

MJ

[Mike J]

Making progess. Hope to have draft one for a critique.

Please be gentle. I hope that you can provide insight into better methods of data manipulation and movement, thoughts about "what if...".

Thanks

MJ