database structure (normalization) question

[webgodjj]

I am creating a database backend for a site. The Top of each page has a title, some text, and a main photo. The bottom of the page has 4 sub categories, each with a photo, a catagory title, and a few links for each category (some sub categories, however, don't have additional links...

So, here is where I'm stuck..

table page
----------------
pid mediumint(10)
maintitle varchar(60)
maindescription text
mainphoto varchar(100)

Now do I need to add one or two more tables to this structure??? and if so, what is the way to creat this.. ie primary keys, foriegn keys, ...

sub_cateory
--------------
sid mediumint(10)
pid mediumint(10)
subphoto varchar(60)
subtitle varchar(60)

sub_cateory_links
--------------
slid mediumint(10)
sid mediumint(10)
link_title varchar(60)
link_url varchar(255)

[r937]

your table design is fine

define the primary/foreign keys like this --

create table page
( pid integer not null primary key 
, maintitle varchar(60) not null
, maindescription text
, mainphoto varchar(100)
)

create table sub_category
( sid integer not null primary key 
, pid integer not null
, foreign key (pid) references page (id)
, subphoto varchar(60)
, subtitle varchar(60)
)

create table sub_category_links
( sid integer not null
, link_url varchar(255) not null
, primary key (sid, link_url)
, foreign key (sid) references sub_category (sid)
, link_title varchar(60)
)

[webgodjj]

hey r937, I remember your posts from a year ago

anyways, I thank you a bunch for the data design... however, this leads me to other questions. Mostly because I don't understand the use of foreign keys..
foreign key (pid) references page (id)

So when I design my php to add, delete, and modify... Do I do anything different? Ie.. can foreign keys help with php design? If I am correct (and I could most likey not be) I thought mysql wasn't like larger databases.. that is, tables can't automatically update themselves from other tables?? Am I making sense? I have created alot of database websites, however, I never seem to tie in foreign keys or understand the importance....

[webgodjj]

HEre's a question... would I want to make another id field on the sub_category_links table for the primary key to keep it sepperate from the others? ie.. How is a sid a primary key for this table if most of them will have the same primary?

your table design is fine

define the primary/foreign keys like this --

create table page
( pid integer not null primary key 
, maintitle varchar(60) not null
, maindescription text
, mainphoto varchar(100)
)

create table sub_category
( sid integer not null primary key 
, pid integer not null
, foreign key (pid) references page (id)
, subphoto varchar(60)
, subtitle varchar(60)
)

create table sub_category_links
( sid integer not null
, link_url varchar(255) not null
, primary key (sid, link_url)
, foreign key (sid) references sub_category (sid)
, link_title varchar(60)
)

[r937]

...I don't understand the use of foreign keys..
foreign key (pid) references page (id)

that's okay, they aren't often used by mysql programmers

if you want relational integrity you would have to use innodb tables

if you do not enable relational integrity by declaring foreign keys, your application has to take care of those details

you know, simple stuff like an order from a non-existent customer, or a post in a non-existent thread, et cetera

[r937]

HEre's a question... would I want to make another id field on the sub_category_links table for the primary key to keep it sepperate from the others? ie.. How is a sid a primary key for this table if most of them will have the same primary?

sid is not "a" primary key

sid is part of the primary key of sub_category_links

a table can have only one primary key, but it may be a composite key

no, sub_category_links does not need an additional column

[webgodjj]

Ok... I get is a little.. and a little not. I have never used innodb databases. Can you show me an example (php?) how this is done by the database compared to writing it in php done by the application? I am REALLY green in this area.

I usually use one table to handle most of my designs and can get away with it.. but want to learn more. Every site I design now uses php/mysql.

that's okay, they aren't often used by mysql programmers

if you want relational integrity you would have to use innodb tables

if you do not enable relational integrity by declaring foreign keys, your application has to take care of those details

you know, simple stuff like an order from a non-existent customer, or a post in a non-existent thread, et cetera

[r937]

sorry, my friend, you'll have to get someone else to teach you

i do coldfusion, not php