Hi all
I am about to make one MySQL table called Catalogue, which will be catalogue for one bookshop. My dilemma is this:
Considering some books have multiple authors, would it be better to create multiple fields in one row(Author1, Author2) or put all authors for the particular book in one field and let's say separate them with comma. I am asking this so I won't need to restructure my whole database when implementing search engine or something advanced. So the question is, what is more convenient?
MySQL table structure question
Moderator: General Moderators
Re: MySQL table structure question
Neither of them
You have to normalize your DB.
Remove all author related fields from your books table.
Create an authors table where you put all data for each author (no book related data in this table).
Create a "relational-only" table with two columns - author_id and book_id.
Create appropriate INDEXies on each table.
So, by JOINing these three tables you can fetch all the data you need.
Also, you may find GROUP_CONCAT useful
Remove all author related fields from your books table.
Create an authors table where you put all data for each author (no book related data in this table).
Create a "relational-only" table with two columns - author_id and book_id.
Create appropriate INDEXies on each table.
So, by JOINing these three tables you can fetch all the data you need.
Also, you may find GROUP_CONCAT useful
There are 10 types of people in this world, those who understand binary and those who don't
Re: MySQL table structure question
Thanks for your reply.
That sounds a little complicated but interesting. The thing is, I want to make the data entry as easy as possible for my client.
I would also have to create the third table for the prices then?
That sounds a little complicated but interesting. The thing is, I want to make the data entry as easy as possible for my client.
I would also have to create the third table for the prices then?
Re: MySQL table structure question
It may look like it's complicated but when you start building complicated (or just specific) queries you will see that this structure will simplify everything.
Prices ... it depends on whether you have different prices per book (e.g. you have several discount groups) or you have a single price per book.
If it's the second case you can put a price field in the books table. If it's the first case - do the same as with the authors-books case and include the price in the 'relational' table.
Prices ... it depends on whether you have different prices per book (e.g. you have several discount groups) or you have a single price per book.
If it's the second case you can put a price field in the books table. If it's the first case - do the same as with the authors-books case and include the price in the 'relational' table.
There are 10 types of people in this world, those who understand binary and those who don't