Mysql table scheme for featured / emphasized products

Question

Well my problem is that I would like to store some kind of products in their own tables and from these products I would like to select some into a featured / emphasised products table which would store the ID of the selected rows from each table to display those featured products on the home page of a website.

After selecting the featured products from their tables I would like to make my own order how they will be listed, so I think a new table is needed where I can store the ID and the order of the featured products, but I can't imagine how to connect these tables properly.

I was thinking a lot how to solve this problem but, I hope somebody will know the correct answer!

Answer 1

Create a second table:

CREATE TABLE FEATURED_PRODUCTS (
    ID INTEGER NOT NULL,
    PRODUCT_ID INTEGER NOT NULL,
    PRODUCT_ORDER INTEGER NOT NULL
)

Then just join with your original table when you want to find your featured products:

SELECT P.*
FROM PRODUCTS P 
    INNER JOIN FEATURED_PRODUCTS FP ON P.ID = FP.PRODUCT_ID
ORDER BY FP.PRODUCT_ORDER

Its probably worth stating that ID in the FEATURED_PRODUCTS table is not strictly necessary, I just don't like having tables without primary key columns.

---- EDIT ----

More complete example:

CREATE TABLE FRUIT_PRODUCTS (
    ID INTEGER NOT NULL,
    NAME VARCHAR(255),
    PRICE INTEGER NOT NULL,
    FARM_OF_ORIGIN VARCHAR(255)
)

CREATE TABLE BREAD_PRODUCTS (
    ID INTEGER NOT NULL,
    NAME VARCHAR(255),
    PRICE INTEGER NOT NULL,
    TYPE_OF_GRAIN VARCHAR(255)
)

CREATE TABLE MEAT_PRODUCTS (
    ID INTEGER NOT NULL,
    NAME VARCHAR(255),
    PRICE INTEGER NOT NULL,
    ANIMAL VARCHAR(255)
)

CREATE TABLE FEATURED_PRODUCTS (
    ID INTEGER NOT NULL,
    TABLE_NAME VARCHAR(255),
    PRODUCT_ID INTEGER NOT NULL,
    PRODUCT_ORDER INTEGER NOT NULL
)

Then you could join them all thusly:

SELECT FP.TABLE_NAME, P.ID, P.NAME, P.PRICE, P.FARM_OF_ORIGIN, 
    NULL AS TYPE_OF_GRAIN, NULL AS ANIMAL
FROM FEATURED_PRODUCTS FP 
    INNER JOIN FRUIT_PRODUCTS P ON FP.TABLE_NAME = 'FRUIT_PRODUCTS' 
        AND FP.PRODUCT_ID = P.ID
UNION
SELECT FP.TABLE_NAME, P.ID, P.NAME, P.PRICE, NULL AS FARM_OF_ORIGIN, 
    P.TYPE_OF_GRAIN, NULL AS ANIMAL
FROM FEATURED_PRODUCTS FP 
    INNER JOIN BREAD_PRODUCTS P ON FP.TABLE_NAME = 'BREAD_PRODUCTS' 
        AND FP.PRODUCT_ID = P.ID
UNION
SELECT FP.TABLE_NAME, P.ID, P.NAME, P.PRICE, NULL AS FARM_OF_ORIGIN, 
    NULL AS TYPE_OF_GRAIN, P.ANIMAL
FROM FEATURED_PRODUCTS FP 
    INNER JOIN MEAT_PRODUCTS P ON FP.TABLE_NAME = 'MEAT_PRODUCTS' 
        AND FP.PRODUCT_ID = P.ID

Which would give you a result set containing all the featured products. Note that this is untested, but should get the idea across.