Very complex database

[orange]

A few comments.
When you see fields in a proposed table such as Size1Qty, Size2Qty.....Size12Qty, - it sends a message that this is probably NOT NORMALIZED.
You could consider a more general (abstract) table (my terminology just to deal with concepts)

Ordered_OutPut
ID (pk)
O_Size
O_Qty

with data such as

1 1 10 ' size 1 10 units
2 2 15 ' size 2 15 units
3 4 20 ' size 4 20 units
4 5 12 ' size 5 12 units

OR

Using your Variants idea (an variant is not a good Term because it has special meaning in Access) Let's call it MyVariant for these comments

You could have a Var_Table
id (pk)
VarValue
with data
1 Colour
2 Shape
3 Size...
and a Colours_Tbl
id
ColValue

with data
1 Red
2 Blue
3 Green
2 Black...


and a Shapes_Tbl

id
Shape_Value

with data


1 Flat
2 Square
3 Oval
4 Round.....

and a Sizes_tbl

id
Size_Value

1 1
2 2
3 4
4 5.......
and with this sort of set up your Ordered_Output_Tbl could evolve to (again concepts NOT TESTED)

id
O_VarSize
O_VarColour
0_VarShape
O_QTY

1 1 3 2 'id 1 size 1 colour Green Shape Square

These are just some thoughts for consideration. Some may make NO sense.

You would have to identify what "variants" you deal with and set up tables and relationships that make sense in "your business".

Others may have different views or additional comments.

Good luck.

[adryan_g78]

Dear Orange,
again I must thank you for your answers. Right now it's late in the night and I don't have the patiente to make this, but tomorow I will "sketch" a table in Excel just to show you more precisely what I need. Your idea about making few tables pointing to the same goal it's a very good one, but I can't use it. "My bussiness", as you call it, doesn't allow me to make use of this kind of database. I will get back soon with a proper example made in Excel, maybe you will give me some more good ideas, as you did by now.
Thanks!

[orange]

Dear Orange,
again I must thank you for your answers. Right now it's late in the night and I don't have the patiente to make this, but tomorow I will "sketch" a table in Excel just to show you more precisely what I need. Your idea about making few tables pointing to the same goal it's a very good one, but I can't use it. "My bussiness", as you call it, doesn't allow me to make use of this kind of database. I will get back soon with a proper example made in Excel, maybe you will give me some more good ideas, as you did by now.
Thanks!

If you do not create a proper underlying structure, you will not have a database that supports your needs.
What exactly does this mean?
My bussiness", as you call it, doesn't allow me to make use of this kind of database.

You say you have read and understood the Normalization articles..

[adryan_g78]

Dear Orange,
as I promised, I will send you an example I've made in Excel. Right now I'm tracking the production using several different files made in Excel, linked to each other by formulas. It's working, but later, when the orders will be too many, this kind of "production tracking program" will be too slow and time consuming to use. This is why I really need this database done by latest end of January.
Maybe you will understand what I ment by saying the example you gave me won't fit my needs.

[orange]

Your images are too small to read. I've tried copy to an image program, bust as I expand them they are fuzzy and unreadable.

As for

Maybe you will understand what I ment by saying the example you gave me won't fit my needs.

I want to clarify that I am describing concepts of how you might organize some of the variations of parameters you need in your business. It was an attempt to show how the things of interest(entities) can be separated and organized into tables that can be related to support your business' rules and processes.

You seem to indicate that organizing and normalizing the data and tables can't be done, or isn't something your company will do. To be blunt, there are no shortcuts to proper design. Short cuts, especially poor table structure will come back to haunt you time and again. If you don't have time to design now, you'll never have time once your production increases.

[RayMilhon]

I have to agree with Orange If your company won't allow you to design a Database the correct way they are not just asking but demanding problems. If it was me I'd be looking for a new company.