Database relationships design

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everyone,

I am developing an Access database and I need some help to figure out how to
link the table in the proper way.

I have the tables which look like these

Orders Order_detail Product_categ
Prod_1
OrderID - PK DetailID - PK ProductCategory PK
Prod1ID - PK
ClientID - FK ProductCategory - FK Category
Prod1Name
Delivery_date OrderID - FK
Prod1_description1
..... Quantity
Prod1_description2
.....
Categ - FK

Prod_2
Prod2ID - PK
Prod2Name
Prod2_description
Categ - FK

And so I have 7 products with different number and type of descriptions
(which cannot be compacted in one table for all the products).
This result in having one foreign key - Categ (which is PK in Product
category table) - for 7 different other tables (product 1 to 7).

I am not sure that this is the correct approach of this problem.
Can anyone give me some better solution?

Thanks very much in advance,
Catalin
 
Catalin,

Why can't you put all the products in the same table? Your database
will be a real pain to develop and maintain.

I see that the only difference between prod_1 and prod_2 is a second
description line. You could put all that data for prod_1 and prod_2
into prod_1 and leave the second description line null.

Also, I suggest that for future postings you post your data with the
information this way:

Table_A:
a_id - autonumber - pk
a_name - text

Table_B:
b_id - pk
a_id - int - fk - references Table_A.a_id
b_name - text

This way we will be able to make sense of your structure.

Cheers,
Jason Lepack
 
I post again the tables structures for a better understanding

Orders
OrderID - PK
ClientID - FK
Delivery_date
......


Order_detail
DetailID - PK
ProductCategory - FK
OrderID - FK
Quantity


Product_categ
ProductCategory PK
Category


Prod_1
Prod1ID PK
Prod1Name
Prod1_description1
Prod1_description2
Categ - FK


Prod_2
Prod2ID - PK
Prod2Name
Prod2_description
Categ - FK

a.s.o. until prod_7

Thanks again,
Catalin
 
Dear Jason,

The problem is that the products are totally different and cannot be
described using similar attributes.

Also the number of the products for each category differs a lot. Ex: prod_1
are 90 items, prod_2 are 55 a.s.o.

Thanks,
Catalin
 
Can you show me the structure of the other 5 tables? The examples
that you have given don't illustrate a big difference in attributes.
The number of products for each category does not matter.

The problem that I have is that if you add another "type" (ie. prod_8)
of item then you're going to have to renovate the whole application...
That doesn't sound like fun to me.

Cheers,
Jason Lepack
 
I will give you the exact structure for the product tables:

Prod_1
Prod1ID PK
Prod1Name
Prod1_description1
Prod1_description2
Categ - FK


Prod_2
Prod2ID - PK
Prod2Name
Prod2_description
Categ - FK


Prod_3
Prod3ID PK
Prod3Name
Prod3_description1
Prod3_description2
Prod3_description3
Prod3_description4
Categ - FK


Prod_4
Prod4ID PK
Prod4Name
Prod4_description1
Prod4_description2
Prod4_description3
Categ - FK


Prod_5
Prod5ID PK
Prod5Name
Prod5_description1
Prod5_description2
Prod5_description3
Prod5_description4
Prod5_description5
Categ - FK


Prod_6
Prod6ID PK
Prod6Name
Prod6_description1
Prod6_description2
Prod6_description3
Categ - FK


Prod_7
Prod7ID PK
Prod7Name
Prod7_description1
Prod7_description2
Categ - FK

The descriptions are different for one product to another.
You do not have to worry because there will not be product 8 in the future.

Thanks
Catalin
 
I can store all of that information in one table.

Products:
Prod_ID PK
Prod_Name
Prod_description1
Prod_description2
Prod_description3
Prod_description4
Prod_description5
Categ - FK

Data:
category_id, category_name
1, 'Tools'
2, 'Cars'
3, 'Hats'

prod_id, prod_name, desc_1, desc_2, desc_3, desc_4, desc_5, categ
1, 'Pink Sombrero', 'wide', 'pink', 'round', 'short', 'doesnt fit in
carry-on', 3
2, 'Adjustable Wrench', 'metal', 'fits different sized bolts', , , , 1
3, 'Porsche 911', 'fast', 'red', 'has 4 wheels', , , 2

Cheers,
Jason Lepack
 
Dear Jason,

The description is not the same, so following your model I should have:

Products:
Prod_ID PK
Prod_Name
Prod1_description1
Prod1_description2
Prod2_description
Prod3_description1
Prod3_description2
Prod3_description3
Prod3_description4
Prod4_description1
Prod4_description2
Prod4_description3
Prod5_description1
Prod5_description2
Prod5_description3
Prod5_description4
Prod5_description5
Prod6_description1
Prod6_description2
Prod6_description3
Prod7_description1
Prod7_description2
Categ - FK

So you see that results a very nasty table which will have a lot of columns
empty...

Catalin
 
I need an example of the data that you're storing, because you're not
making any sense.
 
To make the story short I will give you some example.
One of the products is - European hot rolled sections - "Europrofile" =
prod_name field
this is described as "type" = prod2_description as I caled it.

Another product is - rectangular steel pipes = "rectangular_pipes" =
prod5_name field
described with - "side_1_dimension" = prod5_description1
- "side_2_ dimension" = prod5_description2
- "thickness" = prod5_description3
- "longitudinal_weld" = prod5_description4

So if you want to combine the products above I will have:

ProdID - PK
Prod_name
type -used only in 90 rows for europrofiles
side_1_dimension -used in 34 rows for rectangular pipes
side_2_ dimension - same
thickness - same
longitudinal_weld -same
Category -Fk

and the total number of rows will be the sum for every product I have

In conclussion what I want to say is that the fields for product tables are
unique for each product.

Thanks
Catalin
 
You have to remember, we're not there, if you don't give us
information, we can't make logical jumps and figure out what you're
talking about.

This:
prod5_name field
side_1_dimension
side_2_dimension
thickness
longitudinal_weld

is very different from this:
Prod5Name
Prod5_description1
Prod5_description2
Prod5_description3
Prod5_description4
Prod5_description5

Now, I understand your situation.

I don't know what the best way to handle this is off the top of my
head. This might be a situation where the output drives the design.
What are some of the business requirements for this application? What
sort of outputs are you expecting?

Cheers,
Jason Lepack
 
Sorry if I was so confusing.
Indeed it is an awkward situation here.

Well, as I said in the beginning, I have a Clients table, Orders table,
Orderdetail table which I have to link with the products tables (via product
category due to the differences between the products) and to keep track of
the quantities spent on each product and on each customer.

it sounds quite simple but as u may see it is not.

Catalin
 
IMO you are WAY over-thinking this design. The solution has already
(partially at least) been pointed out to you. I believe your issue is that
you are confusing the database layer with the presentation layer. That's a
common mistake if the new designer has mostly used speadsheet programs in the
past. You'll need to break those two layers apart in your mind before you can
make real progress.

I'm not trying to smack you down with this post; I've been in the database
design field for over 35 years.

Kind regards,

Dennis
 
To make the story short I will give you some example.
One of the products is - European hot rolled sections - "Europrofile" =
prod_name field
this is described as "type" = prod2_description as I caled it.

Another product is - rectangular steel pipes = "rectangular_pipes" =
prod5_name field
described with - "side_1_dimension" = prod5_description1
- "side_2_ dimension" = prod5_description2
- "thickness" = prod5_description3
- "longitudinal_weld" = prod5_description4
Click to expand...

This may be a good case for "Subclassing".

You could have one Products table with the fields that are in common for all
products; this table would be related one-to-one to a series of additional
tables with the type-specific fields.

John W. Vinson [MVP]
 
Thank you very much for your answer.

I have one question. What can I do if there are no common fields for these
products.
I think that my example says what I mean. Shall I keep only the name of the
products in one table?

Can u also give me some more details about "subclassing" please ?

Thanks in advance,
Catalin
 
Would it work to have one tblProductDescription with these fields:
ProductID
ProductName
ProductCategoryIDfk
Description1
Description2
.....
DescriptionN

And tblProductCategory with:
ProductCategoryID
ProductCategoryName
FieldName1
FieldName2
....
FieldNameN

Design an input form that changes the captions for the textboxes
depending on the product category. IFAICT, it would work well for
simple, text-based descriptions. If you wanted to apply data-validity
rules to the various fields, it might become unwieldly.

Or... you could even normalize the Descriptions and their fieldnames
by putting them into separate tables.
 
Dear friend,

I did not undeerstand quite well what u want to say.
Could u be more explicit?

Thanks a lot.

Catalin
 
Thank you very much for your answer.

I have one question. What can I do if there are no common fields for these
products.
I think that my example says what I mean. Shall I keep only the name of the
products in one table?

Can u also give me some more details about "subclassing" please ?
Click to expand...

You would need SOME sort of primary key: you must have a "handle" to uniquely
identify the product in the rest of your application. It might be a ProductNo,
a manually assigned product identifier; it might be an Autonumber - but you
will need a primary key.

Subclassing works by using a "master-child" table relationship, but a one to
one relationship rather than the more common one to many. The master table
would have the unique ProductID, the product name, and any other information
which applies to all products. The child table would have the ProductID as its
primary key as well - a Long Integer field if the master table primary key is
an Autonumber - and fields which apply to just that category of product.

A common example is a computer hardware inventory. You might have tables like:

Hardware
HardwareID
LocationID
HardwareType <e.g. Monitor, Printer, Router, Desktop, ...>
<other information that would apply to any kind of hardware
ManufacturerID <who made it>
ModelNumber <manufacturer's identifier>

Monitors
HardwareID
Width
Height
Technology <e.g. CRT, LCD, plasma screen, ...>

Printers
HardwareID
Technology <e.g. laser, inkjet, impact, ...>
PageWidth
PageHeight
Resolution


John W. Vinson [MVP]
 
Back
Top