SitePoint Sponsor |
|
User Tag List
Results 1 to 8 of 8
-
Jan 18, 2005, 14:19 #1
- Join Date
- Nov 2002
- Location
- Madison, WI USA
- Posts
- 448
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
database structure (normalization) question
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)
-
Jan 18, 2005, 14:46 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
your table design is fine
define the primary/foreign keys like this --Code: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) )
-
Jan 18, 2005, 15:20 #3
- Join Date
- Nov 2002
- Location
- Madison, WI USA
- Posts
- 448
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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....
-
Jan 19, 2005, 11:42 #4
- Join Date
- Nov 2002
- Location
- Madison, WI USA
- Posts
- 448
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?
Originally Posted by r937
-
Jan 19, 2005, 14:54 #5
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Originally Posted by webgodjj
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
-
Jan 19, 2005, 14:55 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Originally Posted by webgodjj
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
-
Jan 19, 2005, 15:54 #7
- Join Date
- Nov 2002
- Location
- Madison, WI USA
- Posts
- 448
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
Originally Posted by r937
-
Jan 19, 2005, 15:57 #8
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
sorry, my friend, you'll have to get someone else to teach you
i do coldfusion, not php
Bookmarks