Issue regarding database design and corresponding operations in forms

[Thomasso]

Hello everyone,

I am building a database for a B2B company. That means that it has special requirements:

• There are many products from multiple suppliers
• Each piece has its own serial number which needs to be stored as a unique value for future operations (calibration, servicing, etc.)
• We don’t know if a piece will be initially stored within an order or a delivery (because one can come sooner than the other and it differs, for example we order products based on customer’s order but sometimes we order something just to have it in case someone orders it)

The idea is that when a new piece occurs, it will need to be put into evidence… For example:

Product with serial number 12345 is type: Samsung Galaxy S6 (user will do this through a simple „Add to evidence“ form)

After that, this specific piece will be in evidence and I can either assign it to an order (and set its selling price – remember, it’s B2B and it’s not static) or to a delivery (and set its buying price).


I want to have a „Add a new order“ form, where I will specify a customer, date, etc., and a „Order Details“ subform, where I will type the serial number and it will fill the other fields (the ones already specified, the rest will remain blank – for example when I am creating a delivery, nothing will happen to the selling price because it might not be known yet).


Very important thing is that when I have the main form, it is in the add mode (blank fields and set to create a new record). The problem I am currently having is that the subform is acting like this as well, meaning that when I select the serial number, it says that it cannot be added because of a duplicate value that should be unique (it is not a primary key though).




The error message is: You cannot add or change a record because a related record is required in table SUPPLIERS.


I am not sure what exactly this means. It shows when I click out of the record. This is the thing I am trying to resolve at the moment.


Attached are some pictures in zip file which explain my database model in depth. I translated the important stuff in English for you guys.

pictures.zip

I would be happy to receive any feedback, since this is my first database ever. Is the design correct? Why is it showing the error message? Any other ideas to improve the model, forms, relationships, etc?


Thank you in advance!
Tomas

[CJ_London]

the message is self explanatory. you have created one to many relationships where you are enforcing referential integrity between suppliers and four other tables.

This means you cannot add a record to any of these four tables unless a corresponding supplier record already exists. i.e. you are trying to add a record without specifying a supplier.

Similarly you cannot add a product details record without having corresponding records in order, products and deliveries

[Thomasso]

Thank you :-). I was doing some personal debugging, so I tried to create the records in the tables myself (don't have corresponding forms yet).

First, I created a supplier, then a delivery consisting of products and then I tried to add those products to an order. Still the same error though.

Would you kindly recommend anything?

Thank you so much

Tomas

[CJ_London]

without a detailed description of what you business does and how it works, it is difficult to advise.

The normal structure would be

supplier>orderheader>orderdetails>products
.................................................> delivery

A supplier has many orders. Each order has many rows. Each Row has a product and a delivery

[Thomasso]

If I try to provide the "detailed description", would you be so kind and advise me how you would implement it?

[CJ_London]

I can try.

within the context of your question:
Avoid using technical words (I am a complete stranger who has no idea what your business does), or if you do, what they mean
Avoid referencing what you have done so far - it may be right, it may be wrong, but should not influence the outcome
Explain what the business does,
Explain what the application is required to do within that business
Explain the process it is required to manage,
Explain what the inputs are (manual/uploads from other systems)
Explain the required outputs - report, view on screen etc

Provide some images if it helps to illustrate

An example of something that is not clear

There are many products from multiple suppliers

does this mean 'one supplier has many products' or 'one product can have many suppliers' or 'one product can have many suppliers and one supplier can have many products'?

[orange]

You may get some ideas from this free data model.

[Thomasso]

The company is a distributor of industrial devices (such as humidity and temperature probes) for the Czech Republic. It has several suppliers from around the world and it is acting as a broker/agent, so it actively searches for end customers (typically a farmaceutical company, almost never a single person). There are multiple employees within each supplier and each customer and we need to have them in evidence as well.

Each supplier has his own portfolio of products (for example probe ABC123) but each product is manufactured by only one supplier, so ABC123 is bound to 1 supplier only. Each unit has its own unique serial number (for example there might be 2 ABC123 probes with serial numbers 001 and 002). There is no general pricing, we set prices to every order based on many factors (contracts, discounts, negotiations...), so in the end, probe 001 might be more expensive than probe 002, despite of being the same product. Same goes for the distributor prices while buying products from the suppliers.

Our company is offering those products in the Czech Republic. When an order comes, the company orders those products from the supplier. Delivery times are long (several weeks), which is normal because it's B2B market. Sometimes we order products without them being already ordered (for our needs, predicting future orders, etc.), so we don't know what comes first (database input meaning) - order or delivery.

Products are also being serviced by our company, that might be either a repair or a calibration. Sometimes we have to send the product to the manufacturer directly and sometimes we are able to repair it on our own. Within a repair service, we usually replace some part of the device and we would like to have this in evidence.

What is expected from the database?

• Evidence of the customers and their employees
• Suppliers and their employees.
• Orders overview. (list of orders, add a new order, edit order, delete order)
• Deliveries overview. (the same as orders)
• Employees' shifts (date, from/until, hour salary...)
• Service and calibration information.

Serial numbers are very important, ideally it should be the main parameter while manipulating products within the database. When creating an order, simply type the SN and product will be added to the order. I could then set the price and maybe other parameters of this specific unit. One serial number can naturally be assigned to only one order. Same goes for deliveries. When creating a delivery, again just type the serial number and product will be added. Then set the buying price etc.

Sometimes there is an order for 10 units of the same product (and each unit has the same price). It would be great to set the parameters which are the same and just type the quantity and add them with one click instead of adding them 1 by 1. We would still need the functionality to edit each one's price and other information.

On the finished order details page, we want to see only product type, quantity, price/pc and total price (if it's the same of course), with the possibility to expand it and see the serial numbers. So basically group the units together based on their parameters.

It is possible to have a unit in stock (so there is a delivery bound to it) but hasn't been ordered yet, so it will have the buying price set but not the selling price (or vice versa for similar reason - order but hasn't been delivered yet from the supplier).

The thing which is really important is some kind of a serial number lookup. User just types the SN and presses "show information" button. Then he sees information about the unit. When it was delivered, ordered, by whom, for how much, when it was serviced, calibrated, what were the problems with it and basically everything that had been bound to the unit at some point.

All inputs will be manual (at first, anyway).

Outputs will mostly be forms and when the database is fully functional, I intend to create many reports (monthly overview, sales, working hours, most successful products, etc.). However, that is not the priority at the moment.


If you help me, I shall be forever in your debt . And if I forgot something important, do tell me, I will provide everything you need within an hour.

Thank you!!
Tomas

[Thomasso]

You may get some ideas from this free data model.

Thanks, I will take a look :-)

[CJ_London]

I'll do what I can, but I'm focusing only on the supplier/customer/product problem, not things like timesheets - that needs to be the subject of another thread.

Some clarification required

Each supplier has his own portfolio of products (for example probe ABC123) but each product is manufactured by only one supplier, so ABC123 is bound to 1 supplier only. Each unit has its own unique serial number (for example there might be 2 ABC123 probes with serial numbers 001 and 002).

1. With regards serial number, does this apply to all products that will be in the db? i.e. are there parts such as fixings/brackets which do not have a serial number?

2. And for an invoice from a supplier supplying 10 ABC123 probes will it list each serial number? Or is the serial number on the delivery note? Or is the serial number something you allocate yourself? or do you have to inspect the delivery to determine the serial number?

3. Also to be clear, you use the supplier product code, not your own?

4. And is serial number a number or text?

5. Does storage location in your warehouse matter? if so, is this against the product? or product and serial number?

[Thomasso]

I believe that I can solve those things like timesheets myself, I got developped quite a lot by now but got stuck on the supplier/customer/product problem. So I believe that I am in the right place :-)

Clarification:
1. Hypothetically, there could be a part without a serial number, like a battery. But vast majority of products do have serial number. These products don't need to be in evidence, we could rather write that to a service description... If it doesn't have a serial number, it's not important enough to be in evidence

2. Yes, supplier's invoice lists serial numbers. It's also on the boxes so it can be inspected within the delivery.

3. Yes, we use supplier's product code, we are his official distributor.

4. Mostly numbers, hypothetically could be a text too.

5. No it doesn't matter. All we need is to determine whether a specific unit is currently in the warehouse or isn't.

Honestly, I greatly appreciate your help!
Tomas

[CJ_London]

OK, we can ignore parts without serial numbers.

Moving to the customer order side of things.

A customer orders a range of products, some are in stock (so you can identify a serial number), some are not and need to be ordered (so you can't identify a serial number at that point). So when do you assign a serial number to the customer order? when ordered if in stock? or when goods are dispatched? or a mixture of the two?

I presume when a customer places an order, the products can be from more than one supplier?

Also, is there a compatibility between supplier products - e.g. customer orders a 'probe type 1' and there are two (or more) suppliers who supply type 1 so you could order from one or the other supplier?

[Thomasso]

Would it be possible to add a new order to the database without specifying the serial number and add it when it's available? It should be as soon as possible. Usually we know the serial number when the supplier sends us the invoice.

That is not likely but we can't forbid someone from ordering products from more suppliers, so let's leave the possibility there.

Products are not compatible in any way. They all have the same sort of products which can be taken as substitutes to some extent, but it is a different product "type". In other words, there aren't 2 suppliers that sell "probe type 1".

I have a feeling that the worst thing will be to implement the serial number tracking... But again, thank you very much :-)

Tomas

[CJ_London]

Would it be possible to add a new order to the database without specifying the serial number

yes - I would expect that - if you are talking about the order to a supplier, you won't know it anyway, and if from a customer it couldn't be assigned if it wasn't in stock - so for this you would not have referential integrity

You also have to think about timing, invoices often arrive anything up to a month after delivery (unless you are paying cash), the delivery note to the customer will need to have the serial numbers - so based on what you have said, that means you cannot despatch any goods until you have received the invoice from the supplier. Is that what you expect to happen? I suspect you actually despatch based on whatever serial numbers the despatcher picks from the warehouse - so you therefore have a reconciliation exercise to do between supplier invoice, stock and customer despatch note/invoice - and the table structure will need to cater for this.

you haven't answered these two questions

A customer orders a range of products, some are in stock (so you can identify a serial number), some are not and need to be ordered (so you can't identify a serial number at that point). So when do you assign a serial number to the customer order? when ordered if in stock? or when goods are dispatched? or a mixture of the two?

I presume when a customer places an order, the products can be from more than one supplier?

[Thomasso]

Actually invoices always come soon enough, they even attach a printed version to the delivery itself. But I don't want to work directly with invoices in the database, I just want to manually assign serial numbers

Question 1: ASAP, when I know the serial number. Sometimes that might be at the time or customer's order, sometimes after the delivery comes.

Question 2: That is not likely but we can't forbid someone from ordering products from more suppliers, so let's leave the possibility there.