•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the Database Design section within the Web Development category of DaniWeb, a massive community of 455,864 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,648 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our Database Design advertiser: Programming Forums
Views: 914 | Replies: 5 | Solved
 |
•
•
Join Date: Jul 2008
Posts: 4
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
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!
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!
•
•
Join Date: Apr 2008
Posts: 295
Reputation:
Rep Power: 1
Solved Threads: 41
Posting Whiz in Training
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
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
Information is moving—you know, nightly news is one way, of course, but it's also moving through the blogosphere and through the Internets. I promise you I will listen to what has been said here, even though I wasn't here. Ann and I will carry out this equivocal message to the world. I'm the master of low expectations.
•
•
Join Date: Jul 2008
Posts: 4
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
•
•
•
•
Originally Posted by 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
Thanks for the quick response!
I am missing one thing though, I do not see Author Name at all in your example?
Last edited by elivate : Jul 15th, 2008 at 4:06 pm.
•
•
Join Date: Apr 2008
Posts: 295
Reputation:
Rep Power: 1
Solved Threads: 41
Posting Whiz in Training
•
•
•
•
Originally Posted by elivate
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 !
Information is moving—you know, nightly news is one way, of course, but it's also moving through the blogosphere and through the Internets. I promise you I will listen to what has been said here, even though I wasn't here. Ann and I will carry out this equivocal message to the world. I'm the master of low expectations.
•
•
Join Date: Jul 2008
Posts: 4
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
•
•
•
•
Originally Posted by tesuji
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!
•
•
Join Date: Jul 2008
Posts: 4
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
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!!
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!!
|
•
•
•
•
•
•
•
•
DaniWeb Database Design Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
•
•
•
•
amd avatar backup blue gene breach chips creative daniweb data protection database design development dos energy enterprise europe government hacker hardware ibm ibm. news illustrator it linux medicine memory microsoft navigation news open source openoffice pc photoshop print ps3 recession red hat research security server sql sun supercomputer supercomputing survey tutorials ubuntu web working x86
- Database Design (Computer Science and Software Design)
- Total noob question (Database Design)
- Cities and Districts DataBase - Basic Question (Database Design)
- automated database design (Database Design)
- Advanced mySQL Querys (PHP)
Other Threads in the Database Design Forum
- Previous Thread: Stored Procedures / Functions
- Next Thread: need help with subtype supertype
