store equal records in multiple tables

Question

Hi there,

im developing a simple access application that helps us to order the right products for a project. i have a table for each contractor containing its products. i have a table "favorite-products" that relates to products and gives additional information how and when they should be used.

normally id have a big table (containing all products) that has a contractor-column. i my favorite-products table i could then easyly relate to a product. but here i need to keep the products in separate tables. so whats the best way to connect my favorite-products table with the products in the contractor-tables?

thanks :)

Answer 1

I would create a contractors table, a product table and then a many-to-many linked table contractors to products. Also i would create a favorite-products table in which you can also have a many-to-many contractors to products link for those cases where a product can come from more than 1 contractor

Answer 2

This is not the best design.

You should UNION all contractor tables together and JOIN with the result:

SELECT  *
FROM    (
        SELECT  product
        FROM    contractor1
        UNION ALL
        SELECT  product 
        FROM    contractor2
        UNION ALL
        …
        ) c
JOIN    favorite f
ON      f.product = c.product

You better keep one single table for you products with contractor as a field.

It will be much easier to query and to manage.

Answer 3

So, you'll have a Contractor, Product and Contractor_Product table. Something like (in psuedo-sql):

create table Contractor {
  id int primary key,
  name varchar(50) not null,
  ...
}

create table Product {
  id int primary key,
  name varchar(50) not null,
  ...
}

create table Contractor_Product {
  contractorid int references Contractor(id),
  productid int references Product(id),
  ...,
  primary key contractorid, productid
}

Now, I'm not 100% sure what you want from the "Favorites" table. It may not be a table, but rather a query. Or, maybe you want a table that similar to the Contractor_Product table? Or just another "isfavorite bool default=false" column on the Contractor_Product table?

Hope that helps!