CodeIgniter Forums
Request for advice how to structure a table with possible relationship - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: Request for advice how to structure a table with possible relationship (/showthread.php?tid=42616)



Request for advice how to structure a table with possible relationship - El Forum - 06-13-2011

[eluser]gbar[/eluser]
Hello, I am in a moment of my project, where I have to make choices regarding the division of a database table. I wanted to seek advice from those who are more experienced.

I have an application I'm working on, the products of this application, which are books, I would then be represented on the board, either with their natural data (title, author, price, synopsis, etc.), but I would also include for each produced its meta tag (keywords, description, title).

So far no problem, put in the same form all matters pertaining to the product and relative meta tags.

The thing that I have some doubts, is how to structure the database table books.

At present the situation of the table is this: - Solution 1

Code:
---------------------------------------------------------------------------------------
| id_book | author_id | category_id | publisher_id | book | price | synopsis | status |
---------------------------------------------------------------------------------------
|   int   |    int    |     int     |     int      |varcha| float |   text   |   int  |
---------------------------------------------------------------------------------------

Having to add fields for meta tags, I do not know whether to add to the existing table, then add three fields:

Code:
------------------------------------------------------------------------------------------------------------------------
| id_book | author_id | category_id | publisher_id | book | price | synopsis | status | Title | Description | keywords |
------------------------------------------------------------------------------------------------------------------------
|   int   |    int    |     int     |     int      |varcha| float |   text   |   int  |varchar|   varchar   | varchar  |
------------------------------------------------------------------------------------------------------------------------

Or create a separate table, called for example meta_books and report back to the table through the field to join book_id.

Code:
------------------------------------------------------
| id_meta | book_id | Title | Description | keywords |
------------------------------------------------------
|   int   |   int   |varchar|   varchar   | varchar  |
------------------------------------------------------

By choosing first solution, I do not think of violating the rules of integrity, as none of the new data will never be repeated, but perhaps the solution to detach the database tables might give me a little bit faster.

I'd like some opinions by yours

Thank you and good job


Request for advice how to structure a table with possible relationship - El Forum - 06-13-2011

[eluser]Svante Hansson[/eluser]
I would go with solution two mainly for avoiding to clutter up a table with transitively dependent values.
Check out third normal form (http://en.wikipedia.org/wiki/Third_normal_form)

Good luck!


Request for advice how to structure a table with possible relationship - El Forum - 06-13-2011

[eluser]gbar[/eluser]
then you split the table into two parts, books and meta_books, if I understand it ... (English is not my first language)

Code:
---------------------------------------------------------------------------------------
| id_book | author_id | category_id | publisher_id | book | price | synopsis | status |
---------------------------------------------------------------------------------------
|   int   |    int    |     int     |     int      |varcha| float |   text   |   int  |
---------------------------------------------------------------------------------------

and

Code:
------------------------------------------------------
| id_meta | book_id | Title | Description | keywords |
------------------------------------------------------
|   int   |   int   |varchar|   varchar   | varchar  |
------------------------------------------------------

Is correct?
Thanks for the clarification


Request for advice how to structure a table with possible relationship - El Forum - 06-13-2011

[eluser]Svante Hansson[/eluser]
Looks about right, you could split up further but this is alright and acceptable Smile
If anyone else has any suggestions/comments feel free to write


Request for advice how to structure a table with possible relationship - El Forum - 06-13-2011

[eluser]gbar[/eluser]
Give me an example of how I could further divide ... I think the synopsis can be put in the destination table, or even in another ...