CodeIgniter Forums

Full Version: Request for advice how to structure a table with possible relationship
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

[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

El Forum

[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!

El Forum

[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

El Forum

[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

El Forum

[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 ...