1 Topic by CDB (edited by CDB 2020-04-14 10:15:02)

  • CDB
  • Member
  • Offline
  • Registered: 2019-12-16
  • Posts: 370

Topic: General question on database schema

Hello again,

I'm about to start on my BIG order system project, and before I go too much further I thought I'd just sound out other peoples thoughts.


I have the problem that an item let's call it 'MyWidget' can be purchased from more than one supplier and therefore each supplier will have a different order code.


I think I've solved this by having a table called 'product_supplier' which consists of a foreign key supplierID from the suppliers table and a similar ID from the products table and the other entry will be the 'supplier part number'.


Now when a new product is sourced, the products table is filled with the product details and FK_ID's to the part_suppliers_table and the supplier part number to the product_supplier table. 


Does this sound the correct way to account for MyWidget being available from multiple suppliers?


My other question is, I need to be able to account for 'backorders or To Follows', which is going to mean only a part quantity of an item has been delivered.  Would it be best to create a 'backorder' table or just use a calculated field to work out quantity received  - quantity ordered?


Any thoughts?


I've attached the database schema for reference.

Post's attachments

Attachment icon db_schema.PNG 142.02 kb, 103 downloads since 2020-04-14 

On a clear disk you can seek forever
  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,676

Re: General question on database schema

Hi CDB,
Just had a quick look at your schema and I'd do exactly the same as you're suggesting with the 'product_supplier' table to get around the same part coming from different suppliers with their own part code.
However, there are a couple of things I'm not sure about (but obviously I don't know much about your project so some of my comments you might have already got covered).
1.  What is the purpose of the 'manufacturers' table?  I guess I'm not clear about the distinction between a manufacturer and a supplier but it seems a bit 'stuck out on a limb' and not doing much!.
2.  I can't see how you can tell from either the 'orders' table or the 'order_products' table who the actual supplier is.
3.  I would definitely go with a 'back-order' or 'deliveries' table (in a 1:many relationship with the 'order_products' table).  Your point is valid that you could use a calculated field to work out the balance due but you wouldn't be able to capture details like the delivery date, delivery quantity and delivery note reference of each part delivery, nor if a delivery needs to be returned for any reason ('over ordered', 'defective' etc). 
4.  I might also use a 'status' table which could be shared by both the 'order' table and the 'order_products' table - something like ('Order Placed, Order In Progress, Order Complete, Ordered Item Outstanding, Ordered Item Part Delivered', 'Ordered Item Fully Delivered',  'Ordered Item Returned, etc etc).  Most statuses could be maintained automatically in a script.
Just a few thoughts and good luck.
Derek.

  • CDB
  • Member
  • Offline
  • Registered: 2019-12-16
  • Posts: 370

Re: General question on database schema

Hi Derek,

Thanks for looking at the schema.

1. The manufacturer is the company that supplies the parent part, the supplier is a company that supplies replacement parts as a 3rd party - so for example - you could have a car and the manufacturer is Austin (yup it dates me), now replacement parts could be purchased from Austin or  from a variety of 3rd party after market suppliers. Although mine is to to with electronics.


2. I thought that they would be tied via the foreign keys combined between the Products and Order_Products tables, but perhaps I should  the supplier and manufacturer FK into the actual orders table.


4. I had considered having a status column, hadn't thought of a separate table, I'll have to think about that. I was thinking along the lines of having back order entries show up as a different colour, which  I suppose could be easier to do.

One of the reasons that I separated out suppliers and manufacturers is that one of the queries/view will have nothing to do with ordering. It will be a reference so the parent part number is entered and all the available substitute parts can be listed.

A real example:

Parent part is an LCD monitor - I look up the internal part number and this will now display a list of all the spare parts that will go into that particular monitor, their internal part numbers and the choice of suppliers.

12345 LCD Monitor   - parent part   - Manufacturer  some - brand - name
  304123 Backlight inverter_1  -  subordinate part - from supplier A  supplier part no: 123-9876
  304124 Backlight inverter_2         "   from supplier A or even supplier B     part no: 123-4567  and INV26
  304155 scalar Card 

etc.     

Originally I was going to make two programs, but that would  mean entering part numbers etc into two different programs, when I hope I can just have one program and just have different forms to display orders and product reference.

On a clear disk you can seek forever
  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,676

Re: General question on database schema

Hi CDB,
Understand now about the manufacturer table (and yes, Austin dates me too - LOL!).
Derek.