Relational DB

[toxictoad]

Hey all, I've asked this a while back and was pointed to a tutorial and since have read various info on relational databases although I still am unsure if I understand it right.

 

I'll try and lay it out the way i do understand it and hopefully you'll be able to follow and help.

 

The database is to store prices on various products.

 

Database name: am_pricing

Table: Categories, name: amp_cats

Fields: PK | Cat | FK

 

E.g:

____________

01 | pri  | 01 |

02 | pro | 02 |

03 | ba  | 03 |

--------------

 

Table: Printing, name: amp_pri

Fields: PK | Type | FK

(The PK here is the first FK from amp_cats)

 

E.g:

___________

01 | bc | 01 |

01 | bo | 02 |

01 | cg | 03 |

-------------

 

Table: Printing, Business Cards, name: amp_pri_bc

Fields: PK | Type | FK

(The PK here is the first FK from amp_pri)

 

E.g:

___________

01 | re | 01 |

01 | fc | 02 |

-------------

 

Table: Printing, Business Cards, Regular, name: amp_pri_bc_re

Fields: PK | Type | FK

(The PK here is the first FK from amp_pri_bc)

 

E.g:

___________

01 | p  | 01 |

01 | g  | 02 |

01 | m | 03 |

-------------

 

Table: Printing, Business Cards, Plain, name: amp_pri_bc_re_p

Fields: PK | Colour | FK

(The PK here is the first FK from amp_pri_bc_re)

 

E.g:

___________

01 | 1 | 01 |

01 | 2 | 02 |

------------

 

Table: Printing, Business Cards, Plain, 1 Colour, name: amp_pri_bc_re_p_1c

Fields: PK | Quantity | Price

(The PK here is the first FK from amp_pri_bc_re_p)

 

E.g:

___________

01 | 250    | 38  |

01 | 500    | 49  |

01 | 1000  | 65  |

01 | 2000  | 99  |

01 | 5000  | 199 |

01 | 10000 | 299 |

-----------------

 

That's a lot of tables and that's just a little bit of it (1 of 5 main categories for pricing) but is this right? I really hope so!

 

Thanks

[Mchl]

You're mixing primary keys and foreign keys concepts.

Primary key (PK) should be unique for each table.

Foreign key (FK) should be a 'pointer' to a PK in another table.

 

 

Database name: am_pricing

Table: Categories, name: amp_cats

Fields: CatID | Cat

 

E.g:

________

01 | pri  |

02 | pro |

03 | ba  |

---------

 

 

Table: Printing, name: amp_pri

Fields: PriID | Type | CatID

 

E.g:

___________

01 | bc | 01 |

02 | bo | 01 |

03 | cg | 01 |

-------------

[toxictoad]

ah I see ok thank you Mchl that makes it a lot clearer.

Apart from that, is this the correct way to build the table structure?

[Mchl]

I don't know the details of your database, so it's hard to say. Basically you should follow the rules for data normalisation (there should be an adequate link in a sticky on top of this forum). There's nothing wrong with having lots of 'slim' tables as long as you know why you're doing this.

[toxictoad]

I don't know the details of your database, so it's hard to say. Basically you should follow the rules for data normalisation (there should be an adequate link in a sticky on top of this forum). There's nothing wrong with having lots of 'slim' tables as long as you know why you're doing this.

 

Thanks Mchl, that's the sticky I went to before and followed the info on http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

 

My database would basically follow the same structure as what I posted but for each field in the amp_cats table (only  names and prices will change. I understand the reasons for doing it this way and can see from this design that it will reduce the data entered and reduce the possibility of errors but other than that I guess that's all I understand.

 

I still have no idea how they join together but that's for another post

[toxictoad]

You're mixing primary keys and foreign keys concepts.

Primary key (PK) should be unique for each table.

Foreign key (FK) should be a 'pointer' to a PK in another table.

 

 

Database name: am_pricing

Table: Categories, name: amp_cats

Fields: CatID | Cat

 

E.g:

________

01 | pri  |

02 | pro |

03 | ba  |

---------

 

 

Table: Printing, name: amp_pri

Fields: PriID | Type | CatID

 

E.g:

___________

01 | bc | 01 |

02 | bo | 01 |

03 | cg | 01 |

-------------

 

Following your example would I therefore need a new field in the Printing table for there to be a foreign key or can I use the primary key from this table to be the foreign key in the following table?

[Mchl]

I can't say I follow... Which two tables you're talking about?

[toxictoad]

The first table amp_cats holds the name of each category, each category then has a product line and prices on each.

 

So the line to the price of 250 business cards goes like this

 

amp_cats > amp_pri > amp_pri_bc > amp_pri_bc_re > amp_pri_bc_re_p > amp_pri_bc_re_p_1c where the final table holds the quantity and price for each set of 1 colour business cards.

 

So in your example

 

the first table starts like this (amp_cats)

 

PK | ser

---------

01 | pri  |

02 | pro |

---------

 

The next is (amp_pri)

---------------

PK | type | FK |

---------------

01 |  bc  | 01 |

02 |  bo  | 01 |

---------------

 

But the only value in amp_pri that can be used as an FK is the PK from amp_pri to the FK of amp_pri_bc. So can I just use the PK or do I have to create a new field for the exclusive purpose of creating a FK in amp_pri_bc?

 

Also in the very last table in the first post was going to be like this (after amending the PK)

 

PK | Quantity | Price

--------------------

01 |    250    | 38

02 |    500    | 55

03 |    1000  | 65

 

But if I understand the normalization right, I don't need that PK as I would be able to use the Quantity field as it's a unique number for each record. Is this correct?

 

So it would be like this

 

PK    | Price

-----------

250  | 38 |

500  | 55 |

1000 | 65 |

-----------

etc

 

Am i on the right track?

[Mchl]

But the only value in amp_pri that can be used as an FK is the PK from amp_pri to the FK of amp_pri_bc. So can I just use the PK or do I have to create a new field for the exclusive purpose of creating a FK in amp_pri_bc?

 

No. That's exactly the purpose of PK. It acts as a unique row identifier, to which foreign keys can point from other tables.

 

But if I understand the normalization right, I don't need that PK as I would be able to use the Quantity field as it's a unique number for each record. Is this correct?

 

Though it might seem in accordance with normalisation rules, it is not such a good idea. Primary keys in general should not be created on values, that have any meaning. Imagine you wish to change the quantity of your cards. If you have a separate PK key - it's easy. If you use quantity as PK... you're likely to have some problems.