Tracking products by their given serial number

[Thomasso]

Hello everybody,



I am designing a company database right now and need to validate something.

I've got tables:
- Orders
- Deliveries
- Products
- Product Details

Now, what I need to do... In the Products table, there will be all product types, with their name, parameters, picture, dimensions etc. But I need to track every single product by its serial number (servicing, being able to look at the S/N and tell when it was delivered, who ordered it etc.).

Hence, the Product Details table will have a foreign key to a record in the Products table, its serial number, buying price (connected to a Delivery) and a selling price (connected to an Order).

I also need to have a form for creating orders, along with adding multiple products (with their serial numbers).

Am I doing it correctly or would you recommend a different approach?


Thank you very much!
Thomas

[orange]

Create a plain English description of the Business processes you are trying to support with this database.
Identify the "things" involved --these will become tables.
Identify how things are related--- 1 Order could be for Many Products.
One Order could relate to Many Deliveries -if there was a back Order on 1 or more Products

be cautious of where and how you assign Price(s) to Orders. If you pick the wrong spot, then whenever you change the Price of a Product you will also change all previous Orders.

I think you are missing an OrderDetails table, but I don't know your business as well as you.

Build a model. and test it with some test data.

Good luck.

[Thomasso]

Thanks for the answer. Ok, let's try plain English :-) ...

It's a B2B business which supplies industrial devices. There are many types of those and each has its own unique serial number. After an order, we order the devices from the manufacturer (usually). Its price differs, depending on the customer, negotiations, etc.

Therefore, my plan is to store this kind of data in respective tables:

Orders table:
- OrderID
- CustomerID
- SupplierID
- and many others

Deliveries table:
- DeliveryID
- SupplierID
- many others

Products table:
- ProductID
- SupplierID
- Name
- Picture
- Weight
- Dimensions
- Description
- Material
- and more (basically overall description of that particular product in general)

ProductDetails table:
- Serial number (primary key)
- ProductID (product type from Products table)
- DeliveryID
- OrderID
- Buying price (buying costs will be calculated from this)
- Selling price (profits will be calculated from this, remember that price differs so I cannot assign it to the product type)

My idea is that I will build a "Create new order" form, there I will be able to fill the basic info (choose a customer, date, etc.), then add products in a subform (specific units from the Product details table along with the price).

I hope that the English is plain enough :-) I know, this type of business is quite unique. Is this approach correct or do you see any flaws I might run into in the future?

Again, thanks very much!
Thomas

[orange]

Here's a free data model that may be helpful.

[Thomasso]

Thanks for the reply. That's not what I am looking for though. All I need is your (or someone else's) personal opinion about my solution, whether it is good or not :-) Thank you.

Thomas

[orange]

Your ProductDetails table should be OrderDetails. It is the details/line items of the order.
Only you will know whether it is good --meets your requirements and supports the intended processes.
The rest of us do not know the details of your business.

Have you tried test data and test scenarios? Does it meet your requirements?

[Thomasso]

That's true I suppose. Honestly, it's the first database I've ever tried to build. Testing with some demo data seems like a great idea, will definitely do that and report back in case someone else is dealing with a similar issue :-) Thank you for your help.

Cheers,
Thomas

[orange]

Here is a link that I often refer to posters. It helps put more meat on my previous post.
You may also find this post useful.
Good luck.

[Thomasso]

Those are very useful links, thank you very much!

I'll be back and will report about further development of my database.

Thomas

[ssanfu]

Suggestions:
I suggest (and really hope) you DO NOT use spaces in object names.
"Name" is a reserved word in Access and shouldn't be used as an object name.

In ProductDetails table:
I would NOT use the "Serial Number" as the primary key. I would expect the serial number to be a Text data type. In my tables, I have an autonumber type field as the PK field.