Help with table design

[jtsymbo]

Bear with me I'm new at this. I'm having some trouble designing a table that ranks books read by a book club each month. Each reader would rank the assigned books (normally 3) in descending order, 3 being the best.The following output is the result I'm looking for.

Rank

Jim

Bob

3

A

B

2

C

D

1

B

A

 

This is the table design I came up with. I seems to be efficient:

key

Name

Rank 3

Rank 2

Rank 1

1

Bob

B

D

A

2

Jim

A

C

B

 

The above table will work for most months when we will be reading 3 books. But how about a month when we will only be reading 2 books, we would only be ranking only 2 books. With the above table design, there would be a null in the rank 3 row for that month. I would prefer not to have nulls. I thought about it and redesigned the table (below) to be more flexible so that we could read any number of books on any given month but it is not nearly as efficient, i.e. to much redundancy for each column. It takes 6 rows to convey the same amount of information as the above table of 2 rows. Is this a problem, and if so, what should I do? Please help. Thanks!!

 

Key

Name

Rank

Book

1

Bob

3

B

2

Jim

3

A

3

Bob

2

D

4

Jim

2

C

5

Bob

1

A

6

Jim

1

B

 

 

[fenway]

The latter table design is the best -- good job!

[jtsymbo]

Thanks Fenway! But you don't think there are too many repeating rows in the revised table?

[jtsymbo]

Now using the above revised table design, I would like to use a form so an individual user could insert new records into the table like this:

 

Auto Key

Rank

Bob

1

3

B

2

2

D

3

1

A

If possible I would like the descending numbers of the ranking to be auto generated, so that the user just fills in the name of the books (D,B,A). But I'm confused about the php programing to get values into the table, because the structure of the form is different than the structure of the table. Thanks for any help!!

 

[fenway]

Thanks Fenway! But you don't think there are too many repeating rows in the revised table?

There aren't any repeated rows... they're all different.

 

Now using the above revised table design, I would like to use a form so an individual user could insert new records into the table like this:

 

Auto Key

Rank

Bob

1

3

B

2

2

D

3

1

A

If possible I would like the descending numbers of the ranking to be auto generated, so that the user just fills in the name of the books (D,B,A). But I'm confused about the php programing to get values into the table, because the structure of the form is different than the structure of the table. Thanks for any help!!

 

All you do it have the form have all the book selections, and have a drop-down with the current rank, and all possible ranks -- then simply update the information in the DB.