Relational Database design question...

[elivate]

Hi!

so I am trying to design a relational database and I think I am on the right track, but I am a little confused as to how I will actually update the data...

Normally I build a flat file table and use a web form to add/update/delete data, however, I am trying to build a larger database and this is no longer practical...

This is still not too complex, I think I can accomplish what I want with three tables...

Here is what I have so far:

tbl_Poems:
- poem-ID - pk
- Poem-Title
- Poem
- Author-ID - fk
- Book-ID - fk

tbl_Author:
- Author-ID - pk
- Author-Name

tbl_Book:
- Book-ID - pk
- Book-Name

Obviously I want to create a database containing poems. On the website the user can read the poem. I want them to be able to search by Poem, Author, or Book.

So if this is the proper database design how do I actually input the data using only 1 form?

If this is not correct can someone help me design the tables better and instruct me on how to make this updateable using only 1 form?

Any input or help is really appreciated!

thanks!

[tesuji]

Hello,

in tbl_poems exists a transitive dependency poem-ID -> Author-ID -> Book-ID. Therefore, this table does not satisfy 3NF. fk Author-ID should be removed and inserted into tbl_Book. The result is:

tbl_Poems:
- poem-ID - pk
- Poem-Title
- Poem
- Book-ID - fk

tbl_Book:
- Book-ID - pk
- Book-Name
- Author-ID - fk

your table structure can easily be put on one form because you have two one-to-many relationships (aka master detail tables). So one author may have many books related to. You can select an existing author or enter a new one in the 1st part of your form. In a 2nd part you can then select an existing book of that author or you may enter a new book where the author_ID would then be inserted into the tbl_book. Once selected a book you will see many poems of that book or in 3rd part of you form you may enter new poems. If you store new poem, current book_ID would be inserted in tbl_poem. Graphical element to hold that data can be combo box, grid element etc.

Ok, details depend on the programming language, database, web server you will use to carry out your poem project.

krs,
tesu

[elivate]

Hello,

in tbl_poems exists a transitive dependency poem-ID -> Author-ID -> Book-ID. Therefore, this table does not satisfy 3NF. fk Author-ID should be removed and inserted into tbl_Book. The result is:

tbl_Poems:
- poem-ID - pk
- Poem-Title
- Poem
- Book-ID - fk

tbl_Book:
- Book-ID - pk
- Book-Name
- Author-ID - fk

your table structure can easily be put on one form because you have two one-to-many relationships (aka master detail tables). So one author may have many books related to. You can select an existing author or enter a new one in the 1st part of your form. In a 2nd part you can then select an existing book of that author or you may enter a new book where the author_ID would then be inserted into the tbl_book. Once selected a book you will see many poems of that book or in 3rd part of you form you may enter new poems. If you store new poem, current book_ID would be inserted in tbl_poem. Graphical element to hold that data can be combo box, grid element etc.

Ok, details depend on the programming language, database, web server you will use to carry out your poem project.

krs,
tesu

Thanks for the quick response!

I am missing one thing though, I do not see Author Name at all in your example?

[tesuji]

Thanks for the quick response!

I am missing one thing though, I do not see Author Name at all in your example?

I did not change your table tbl_Author which already has Author-Name. Therefore I left out your 3rd table !

[elivate]

I did not change your table tbl_Author which already has Author-Name. Therefore I left out your 3rd table !

ahhh... I get it thanks!

[elivate]

OK, so I have the db built like this now... and I am now building the forms for submitting the data, this is where I am stuck now...

I am using php and mysql...

I understand how to join the tables when doing the select statement, but I am confused on how to properly build the form to enter the data...

Like I said, I want to use one form where the user can enter the following:

The name of the Poem
The actual content of the Poem
The Author of the Poem
The Book that contains the Poem

Obviously I would need to query the db to see if any of these elements exist, and then determine if the poem is not a duplicate (based on a combo of the Poem title and Author) and in cases where it is a recurring author or book I would show the existing data in a drop-down list, and allow input for the remaining fields...

So, having said all that, I guess the big question is how do I build the SQL INSERT statement to properly submit the data across the three tables?

Thanks!!