MySQL/PHP database concept help

[zantzinger]

I think I can see two areas of my site where MySQL/PHP will be needed and I just wanted someone to point me in the right direction. ((I'm new to webdev and, having created a site a couple of weeks ago using iWeb, I've since decided to take the plunge and learn some html/css/php/mysql. I've only tinkered with the site so far in textwrangler, but some of the iWeb gunk has been removed; e.g. multiple css pages)).

So, there's going to be a history page on www.oxfordpoetry.co.uk/history.html which will pretty much use all the data from http://www.gnelson.demon.co.uk/oxpoetry/index/index.html but package it in a prettier way.

The magazine has issues (and info on them like number of pages, whether there's a preface, table of contents); contributors/editors' names (and info on them like which issues they feature in, who they went on to be).

I can't quite see how to make a database where entries aren't repeated (because some contributors are published in multiple issues) and I don't know whether this is actually a problem. Can anyone help with what the fields would be?


Second use of MySQL and PHP. At the moment the individual pages of the site (only about 5 pages) load up with different quotations underneath the main logo. I think that x number of quotations (a field with lots of characters) and their authors could fill a two-column database and a PHP script could randomly put a quote on a page when it loaded. Refreshing the page would generate a different quote. Is this right?

[MyWebsiteAdvise]

Hi,

You can create separate table for contributors/editors.
editorID
editorFirstName
editorLastName
editorEmail
etc.

Afterwards, you will instert the editorID only in your main table.

About second, you are right. The script will select only one row (2 columns) randomly.

[ariell]

You (whether deliberately or not) tackle the issue of database normalization. Moreover, you mention the "classic case". ONE writer may have written MANY books. On the other hand, there might be ONE book that is written by MANY authors.

You'd certainly will try to avoid to coming up with structures where there's author01, author02... authorn, as well as book01, book02, ...n - most of those columns never (or at least rarely) populated with ANY data.

In DB slang: all u need to achieve to normalize your db (tables) is "circumventing" ANY many-to-many relationship.

like:

tab_books

title
subtitle
isbn
bla bla

tab_authors

firstName
lastName
age
bla bla

tab_link_authors_books

ID
authorID
bookID

EX:
3 authors wrote one book together, the link table then has those entries

ID

[ariell]

sorry...

ID 1
authorID 1
bookID 1

ID 2
authorID 2
bookID 1

ID 3
authorID 3
bookID 1

if author one writes two more books:

ID 4
authorID 1
bookID 2

ID 5
authorID 1
bookID3

and so one...