MySQL table structure question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
LDusan
Forum Commoner
Posts: 45
Joined: Sun Mar 08, 2009 5:03 am
Location: Belgrade, Serbia

MySQL table structure question

Post by LDusan »

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?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL table structure question

Post by VladSun »

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 ;)
There are 10 types of people in this world, those who understand binary and those who don't
LDusan
Forum Commoner
Posts: 45
Joined: Sun Mar 08, 2009 5:03 am
Location: Belgrade, Serbia

Re: MySQL table structure question

Post by LDusan »

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?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL table structure question

Post by VladSun »

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.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply