Is this wisdom? fact tables or not.

Marcusanthonius

I am searching for a effective way to build tables with the following info:

attributes:
Categorie
Name
Type
Kwalification
District
Country
Farmer
Age
%
yeast/dist
short_description
Price

Basic Knowledge of databases thought me I have to normalize so there will be no/less redundancy.

My example contains whisky but also wines.
BALBLAIR, HIGHLAND, 10, 0.70, 40.0, - , - ,DIST, 25.75
BALBLAIR, HIGHLAND, 16, 0.70, 40.0, - ,- ,DIST, 34.95

Because a lot of products differ from only one thing, by example only the bottle size i made the following tables:

CATEGORIE
cat_id
categorie

PRODUCTS
prod_id
name

TYPE
type_id
variety

DESTRICT
destrict_id
appelation

and so on...

then one tabel to combine all these little tables:

COMBINATION
comb_id
prod_id
type_id
destrict_id

Is this method indeed called a coverage table with fact tables?
Is this a wise decision for updating and managing in the future?

In this realtime example I have less redundancy but is it managable?

Thanks in advance.

tunage

Theres no need for the combination table, just add the keys of the other tables to the products table as foreign keys -

PRODUCTS
prod_id
name
cat_id
type_id
destrict_id

CATEGORIE
cat_id
categorie

TYPE
type_id
variety

DESTRICT
destrict_id
appelation

Marcusanthonius

so I got:

PRODUCTS
prod_id
name
cat_id
type_id
destrict_id
alc_id

I think THESE together are necessary to make ONE product identical. (because itteration of name, we sell more then one Balblair whiskies, is not yet, and we have drinks which are al the same except for volume...)

So if I am right,

Country
Farmer
Age
yeast/dist
short_description &
Price

are not necessary for identification and can be stored in an other tables like:

COUNTRY
country_id
prod_id <-?
country

AGE
age_id
prod_id <-?
age

Or would you say every attribute of a product will be attached with foreign keys to PRODUCTS
Am I right when I say this PRODUCTS table with more then 6000 products in the future will be to big/slow?
Or would you say it is smart to put all foreign keys concerning products in one table?

Or is it wise to make all other attributes, like price a different table?

I did some research and made some small databases, but I know i am thinking to difficult now...
)

Marcusanthonius

I have thougt this over and over again,
and came to the following problem...

When I really use this products table with all foreign keys, then I can't use name, right? but only all foreign keys so I need an extra table:

PRODUCTS
prod_id

name_id ex. Balblair
cat_id ex. whiskies
type_id ex. doublewood
destrict_id ex. highlands
alc_id ex. 43.0 %
price_id ex. $ 56.00
disc_price_id ex. $ 34.00
alc_id ex. 43.0 %
alc_id ex. 43.0 %

and a table

NAMES
name_id
name

PRICE
price_id
disc_id
prijs

Because when my name is 'Balblair' I cannot put there another name which is balblair in this table, cause I don't want redundancy...

So,

So I still think the combination table is the true outcome..
Can someone please tell me how to think with the table products?