DB Design
-
I'm designing a database (at which I do not have much knowledge) and I have a question. I want to have a table that has properties, such as title, author, length, etc for books and one of the properties I want is genre (fiction, non-fiction, thriller, etc). Here's my problem, each book could be have than one category so how would I set that up? I want to have the Books table with a Genre field which would be a FK to the Genre table. What I'm not sure about is storing the values because of normalization ie. if I just stored the GenreID's as a comma seperated list in the Genre field wouldn't that break normalization not to mention make it harder to parse...I'm just a little confused with how to design this and any help would be appreciated. Thanks.
- Aaron
-
I'm designing a database (at which I do not have much knowledge) and I have a question. I want to have a table that has properties, such as title, author, length, etc for books and one of the properties I want is genre (fiction, non-fiction, thriller, etc). Here's my problem, each book could be have than one category so how would I set that up? I want to have the Books table with a Genre field which would be a FK to the Genre table. What I'm not sure about is storing the values because of normalization ie. if I just stored the GenreID's as a comma seperated list in the Genre field wouldn't that break normalization not to mention make it harder to parse...I'm just a little confused with how to design this and any help would be appreciated. Thanks.
- Aaron
Don't store the data as a a comma separted list. You need a Book_Genre table that stores the book_id and the genre_id, is covered by a unique index and links to the Book and genre tables on the key fields.
-
I'm designing a database (at which I do not have much knowledge) and I have a question. I want to have a table that has properties, such as title, author, length, etc for books and one of the properties I want is genre (fiction, non-fiction, thriller, etc). Here's my problem, each book could be have than one category so how would I set that up? I want to have the Books table with a Genre field which would be a FK to the Genre table. What I'm not sure about is storing the values because of normalization ie. if I just stored the GenreID's as a comma seperated list in the Genre field wouldn't that break normalization not to mention make it harder to parse...I'm just a little confused with how to design this and any help would be appreciated. Thanks.
- Aaron
This is a many-to-many join that you are describing: One book can have many genres. One genre can contain many books. What you need is an intermediate table. These are necessary to model many-to-many joins. The intermediate tables contains a compound primary key made up of the primary keys of the tables that are being joined together. In this case a Book table and a Genre table. The intermediate table BookGenre will look like this:
BookId int not null PK
GenreId int not null PKTo join a book to a genre you create a new row in the BookGenre table with the corresponding primary keys. The act of making the compound primary key in the intermediate table ensures that you don't have duplicate joins. e.g. Terry Prattchet's Thump listed as Fantasy twice, or vice versa. Does this help?
Upcoming Scottish Developers events: * UK Security Evangelists On Tour (2nd November, Edinburgh) * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
-
This is a many-to-many join that you are describing: One book can have many genres. One genre can contain many books. What you need is an intermediate table. These are necessary to model many-to-many joins. The intermediate tables contains a compound primary key made up of the primary keys of the tables that are being joined together. In this case a Book table and a Genre table. The intermediate table BookGenre will look like this:
BookId int not null PK
GenreId int not null PKTo join a book to a genre you create a new row in the BookGenre table with the corresponding primary keys. The act of making the compound primary key in the intermediate table ensures that you don't have duplicate joins. e.g. Terry Prattchet's Thump listed as Fantasy twice, or vice versa. Does this help?
Upcoming Scottish Developers events: * UK Security Evangelists On Tour (2nd November, Edinburgh) * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
This is exactly what I'm looking for, thanks very much for the help. It makes a lot of sense, but it's not something I would have thought of. Thanks again!
- Aaron