Professional Web Applications Themes

Help! I can't design a decent database! - MySQL

Hi everyone. I am having trouble designing a database that will suit what I need it for. On one hand, I have an efficient method of doing what I need, which gives me a single snag. On the other, I have a not so efficient way of doing it with a few more snags which can all be gotten around with a little more use of the available resources. This is going to be lengthy, so I apologise in advance, but I am also hoping that it might spark off a debate/discussion, as I know there is usually more than ...

  1. #1

    Default Help! I can't design a decent database!

    Hi everyone. I am having trouble designing a database that will suit
    what I need it for. On one hand, I have an efficient method of doing
    what I need, which gives me a single snag. On the other, I have a not
    so efficient way of doing it with a few more snags which can all be
    gotten around with a little more use of the available resources. This
    is going to be lengthy, so I apologise in advance, but I am also hoping
    that it might spark off a debate/discussion, as I know there is usually
    more than 1 correct way of doing things.

    This thread is best viewed using a monospace font.

    I have created a website module that helps users organize and keep
    track of the books that they own on a web-based game on another
    website.

    At present, I have:
    A table which contains the user information (along with a unique user
    ID, and username).

    --------------------------------------
    | uid | username | e-mail | contd...
    --------------------------------------
    | 2 | user123 | com | contd...
    --------------------------------------
    | 3 | user456 | com | contd...
    ---------------------------------------
    | | | |

    A table containing the list of books, as well as their unique ID type
    (as there are different types of books).

    --------------------------------------------
    | book_id | book_name | book_status | contd...
    --------------------------------------------
    | 1 | this is a book | 1 | contd...
    --------------------------------------------
    | 2 | this is a book2 | 3 | contd...
    --------------------------------------------
    | | | |

    (And this next one is the crazy table, but my excuse for using it will
    follow shortly...)
    26 tables containing anything from 6 to 531 columns. 1 column contains
    the unique user ID, and the other colums are labled with the
    appropriate book ID, and contain a simple '0' or '1' to flag whether or
    not a user has a particular book. The default is '0'. There is one
    table for every letter of the alphabet, as each book name begins with a
    letter of the alphabet only (no numbers, hyphons, underscores etc...).

    --------------------------------
    | uid | 1 | 2 | 3 | 4 | 5 | 6 | contd...
    --------------------------------
    | 2 | 0 | 0 | 1 | 0 | 1 | 1 | contd...
    --------------------------------
    | 4 | 0 | 1 | 0 | 0 | 0 | 1 | contd...
    --------------------------------
    | | | | | | | |


    My idea was to be able to get an entire user row from this table, or
    multiple user rows which will be output in my PHP page appropriately.
    There can be up to 100 users listed on any one page. When a certain
    book is queried

    I have been advised, and would also prefer to have just three colums to
    store the users books in:

    -----------------------------
    | uid | book_id | book_status |
    -----------------------------
    | | | |

    So why didn't I do it? First of all, I am somewhat of a complete novice
    to database design, which is no excuse and I accept that, although I
    can't just learn everything perfect first time. Secondly, by doing it
    the way I have it presently, I can set the default column values to
    '0'. This is simply so that rather than see who does have a particular
    book, I can also see who _doesn't_ have a particular book. If I did it
    the sceond way, I can always add each of the books using a script to
    set the value for each new book for the user to '0'.

    This is ok, but there are 3600 books in total, and that's one helluva
    database query! Also, should I ever need to add another book to the
    list. I would have to add another row for the new book, in fact one new
    row for each user, and also set that to zero.

    So why do I have this dumb idea in my head that I need to set it to
    zero? Well, for anyone who isn't thinking along the same lines as I am
    (probably everyone, as I do admit, my current method is rather odd), I
    need to set it to '0', so I can pull a list from the database of users
    who don't have a particular book. If there is no row in the table for a
    particular book that is _not_ owned by a user, then no result will be
    returned. I can only get a list of people who _do_ have a particular
    book. My dilhema is that I can make a script that does everything the
    wrong way round, and gets a list of books, finds out who does have
    them, and then work out who doesn't, but I would imagine that this
    would be a potential resource hog, especially when done for 100 users
    in a single page load.

    Surely there is a better way to do this using the method I would like
    to use as opposed to my current method, which works, but is not as
    effective as the way I would like to do it, that brings with it a
    single, yet major snag. I _need_ to be able to JOIN tables, so the new
    method is most certainly better, but how can I overcome my snag?

    Any input on this would be greatly appreciated. I am certain that I am
    missing something.

    Best wishes.

    Daz

    Daz Guest

  2. #2

    Default Re: Help! I can't design a decent database!

    Daz wrote: 

    Daz,

    Do some searches on "database normalization". That will help you a lot.

    Hint: a table with 531 columns for various books is NOT the way to go.
    A separate table with uid, book_id and status is much better. This
    would also entail having another table with bookid (could be iSBN
    number, for instance), book name, author, etc.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: Help! I can't design a decent database!


    Jerry Stuckle wrote: 

    Hi Jerry.

    Thank you very much for your reply. Ironically I have spent the last
    day looking into database normalization, and I think I have a
    relatively firm grasp as to the principles behind it. It is due to this
    that I had originally decided to completely change the way my database
    works, however, I am just unsure as to how I can add the appropriate
    data without having to to update 3600 rows when a user first uses my
    webpage (1 for each book). If I do it without adding all of these rows
    and setting them to '0', I don't think I could obtain a list of all
    users who don't have a particular book, as the row(s) to reflect it may
    not be there if they haven't been set originally. Would I be right in
    saying this? To my knowledge, I wouldn't be able to get any information
    as the rows needed to get the desired results "might" not exist. I
    would be able to see the books that a user has got, by returning all
    rows for a user where `is_owned` = '1', I am just stuck with how to
    return a list of books that a user hasn't got, as if the row doesn't
    exist, it won't show in my query (unless of course it's set to '0').
    Due to my lack of knowledge on what kind of work load a database can
    handle, I am uncertain of whether it's actually feasible for me to
    create 3600 user rows for each user who uses my webpage.

    I hope this makes sense, and sorry for repeating myself. I am slightly
    frustrated as I know what I mean, but can't seem to put it into words.
    I am still considering my possibilities, but only have limited
    experience with MySQL queries, functions, and inner workings, I just
    wanted to check whether or not there was a logical solution that I may
    have overlooked (possible) or may be unaware of (most likely).

    Ugh... I have typed that many SQL queries today, I keep going to type a
    semi-colon at the end of my sentences, instead of a full stop...

    Thanks again.

    Daz.

    Daz Guest

  4. #4

    Default Re: Help! I can't design a decent database!

    Daz wrote: 
    >
    >
    > Hi Jerry.
    >
    > Thank you very much for your reply. Ironically I have spent the last
    > day looking into database normalization, and I think I have a
    > relatively firm grasp as to the principles behind it. It is due to this
    > that I had originally decided to completely change the way my database
    > works, however, I am just unsure as to how I can add the appropriate
    > data without having to to update 3600 rows when a user first uses my
    > webpage (1 for each book). If I do it without adding all of these rows
    > and setting them to '0', I don't think I could obtain a list of all
    > users who don't have a particular book, as the row(s) to reflect it may
    > not be there if they haven't been set originally. Would I be right in
    > saying this? To my knowledge, I wouldn't be able to get any information
    > as the rows needed to get the desired results "might" not exist. I
    > would be able to see the books that a user has got, by returning all
    > rows for a user where `is_owned` = '1', I am just stuck with how to
    > return a list of books that a user hasn't got, as if the row doesn't
    > exist, it won't show in my query (unless of course it's set to '0').
    > Due to my lack of knowledge on what kind of work load a database can
    > handle, I am uncertain of whether it's actually feasible for me to
    > create 3600 user rows for each user who uses my webpage.
    >
    > I hope this makes sense, and sorry for repeating myself. I am slightly
    > frustrated as I know what I mean, but can't seem to put it into words.
    > I am still considering my possibilities, but only have limited
    > experience with MySQL queries, functions, and inner workings, I just
    > wanted to check whether or not there was a logical solution that I may
    > have overlooked (possible) or may be unaware of (most likely).
    >
    > Ugh... I have typed that many SQL queries today, I keep going to type a
    > semi-colon at the end of my sentences, instead of a full stop...
    >
    > Thanks again.
    >
    > Daz.
    >[/ref]

    But you don't have an entry if they don't have a book. You only list
    the books they have. Searching for books they don't have is as easy as
    using NOT IN (subselect here), for instance. Or a LEFT JOIN between
    user and book, where the book is NULL. Several ways to do it.

    That's because if they don't have the book the rows WON"T exist. And
    that's exactly like you want it.

    Can you imaging an insurance company with millions of customers and
    thousands of possible policies and riders doing it like you're
    suggesting? The enormity of the database would be astounding.

    I do suggest you study up on SQL a little more. It's not that hard, but
    it's not entry level SQL, so you won't find it in some intro books.
    More like second grade SQL :-)

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  5. #5

    Default Re: Help! I can't design a decent database!


    Jerry Stuckle wrote: 

    Ugh... You're a star Jeremy! Just as I suspected, there was a logical
    method of achieving the result I wanted. Oddly enough, I was studying
    JOIN yesterday, and completely forgot about the LEFT JOIN function, and
    what it does. I thought that if the row didn't exist, then I couldn't
    query whether or not the book = NULL.

    However, as you hinted, an extra table will do the trick! It had never
    occured to me to cut one of my existing tables in have, so instead of
    having 3 columns in 1 table, I have 2 tables of 2 columns instead.

    Thank you very much, you have opened my eyes to what MySQL is capable
    of, even more than before. Grab yourself a beer. You've earnt it!

    Daz Guest

  6. #6

    Default Re: Help! I can't design a decent database!

    Daz wrote: 

    I'm not a database expert (not by a long shot) but I managed this:

    mysql> create table u (id int primary key auto_increment, n varchar(12));
    mysql> create table b (id int primary key auto_increment, n varchar(12));
    mysql> create table ub (uid int, bid int);

    mysql> -- some inserts ...

    mysql> -- users
    mysql> select * from u;
    +----+-------+
    | id | n |
    +----+-------+
    | 1 | one |
    | 2 | two |
    | 3 | three |
    +----+-------+

    mysql> -- books
    mysql> select * from b;
    +----+---------+
    | id | n |
    +----+---------+
    | 1 | b-one |
    | 2 | b-two |
    | 3 | b-three |
    +----+---------+

    mysql> -- user/books
    mysql> select * from ub;
    +------+------+
    | uid | bid |
    +------+------+
    | 1 | 1 |
    | 1 | 2 |
    | 2 | 1 |
    | 2 | 3 |
    | 3 | 3 |
    +------+------+

    mysql> -- all users/books combinations
    mysql> select u.id as u, b.id as b from u, b;
    +---+---+
    | u | b |
    +---+---+
    | 1 | 1 |
    | 2 | 1 |
    | 3 | 1 |
    | 1 | 2 |
    | 2 | 2 |
    | 3 | 2 |
    | 1 | 3 |
    | 2 | 3 |
    | 3 | 3 |
    +---+---+

    mysql> -- all users/books combinations with NULLs
    mysql> select * from ub right join (select u.id as u, b.id as b from u, b)
    -> x on uid=x.u and bid=x.b;
    +------+------+---+---+
    | uid | bid | u | b |
    +------+------+---+---+
    | 1 | 1 | 1 | 1 |
    | 2 | 1 | 2 | 1 |
    | NULL | NULL | 3 | 1 |
    | 1 | 2 | 1 | 2 |
    | NULL | NULL | 2 | 2 |
    | NULL | NULL | 3 | 2 |
    | NULL | NULL | 1 | 3 |
    | 2 | 3 | 2 | 3 |
    | 3 | 3 | 3 | 3 |
    +------+------+---+---+

    mysql> -- who doesn't have book 3?
    mysql> select * from ub right join (select u.id as u, b.id as b from u, b)
    -> x on uid=x.u and bid=x.b where b=3 and uid is null;
    +------+------+---+---+
    | uid | bid | u | b |
    +------+------+---+---+
    | NULL | NULL | 1 | 3 |
    +------+------+---+---+

    mysql> -- What books doesn't user 3 have?
    mysql> select * from ub right join (select u.id as u, b.id as b from u, b)
    -> x on uid=x.u and bid=x.b where u=3 and bid is null;
    +------+------+---+---+
    | uid | bid | u | b |
    +------+------+---+---+
    | NULL | NULL | 3 | 1 |
    | NULL | NULL | 3 | 2 |
    +------+------+---+---+

    With some tweaks I'm sure you'll be able to get what you want out of the
    database with inexistent rows.

    Happy SQLing :)

    --
    File not found: (R)esume, (R)etry, (R)erun, (R)eturn, (R)eboot
    Pedro Guest

  7. #7

    Default Re: Help! I can't design a decent database!


    Pedro Graca wrote: 
    >
    > I'm not a database expert (not by a long shot) but I managed this:
    >
    > mysql> create table u (id int primary key auto_increment, n varchar(12));
    > mysql> create table b (id int primary key auto_increment, n varchar(12));
    > mysql> create table ub (uid int, bid int);
    >
    > mysql> -- some inserts ...
    >
    > mysql> -- users
    > mysql> select * from u;
    > +----+-------+
    > | id | n |
    > +----+-------+
    > | 1 | one |
    > | 2 | two |
    > | 3 | three |
    > +----+-------+
    >
    > mysql> -- books
    > mysql> select * from b;
    > +----+---------+
    > | id | n |
    > +----+---------+
    > | 1 | b-one |
    > | 2 | b-two |
    > | 3 | b-three |
    > +----+---------+
    >
    > mysql> -- user/books
    > mysql> select * from ub;
    > +------+------+
    > | uid | bid |
    > +------+------+
    > | 1 | 1 |
    > | 1 | 2 |
    > | 2 | 1 |
    > | 2 | 3 |
    > | 3 | 3 |
    > +------+------+
    >
    > mysql> -- all users/books combinations
    > mysql> select u.id as u, b.id as b from u, b;
    > +---+---+
    > | u | b |
    > +---+---+
    > | 1 | 1 |
    > | 2 | 1 |
    > | 3 | 1 |
    > | 1 | 2 |
    > | 2 | 2 |
    > | 3 | 2 |
    > | 1 | 3 |
    > | 2 | 3 |
    > | 3 | 3 |
    > +---+---+
    >
    > mysql> -- all users/books combinations with NULLs
    > mysql> select * from ub right join (select u.id as u, b.id as b from u, b)
    > -> x on uid=x.u and bid=x.b;
    > +------+------+---+---+
    > | uid | bid | u | b |
    > +------+------+---+---+
    > | 1 | 1 | 1 | 1 |
    > | 2 | 1 | 2 | 1 |
    > | NULL | NULL | 3 | 1 |
    > | 1 | 2 | 1 | 2 |
    > | NULL | NULL | 2 | 2 |
    > | NULL | NULL | 3 | 2 |
    > | NULL | NULL | 1 | 3 |
    > | 2 | 3 | 2 | 3 |
    > | 3 | 3 | 3 | 3 |
    > +------+------+---+---+
    >
    > mysql> -- who doesn't have book 3?
    > mysql> select * from ub right join (select u.id as u, b.id as b from u, b)
    > -> x on uid=x.u and bid=x.b where b=3 and uid is null;
    > +------+------+---+---+
    > | uid | bid | u | b |
    > +------+------+---+---+
    > | NULL | NULL | 1 | 3 |
    > +------+------+---+---+
    >
    > mysql> -- What books doesn't user 3 have?
    > mysql> select * from ub right join (select u.id as u, b.id as b from u, b)
    > -> x on uid=x.u and bid=x.b where u=3 and bid is null;
    > +------+------+---+---+
    > | uid | bid | u | b |
    > +------+------+---+---+
    > | NULL | NULL | 3 | 1 |
    > | NULL | NULL | 3 | 2 |
    > +------+------+---+---+
    >
    > With some tweaks I'm sure you'll be able to get what you want out of the
    > database with inexistent rows.
    >
    > Happy SQLing :)[/ref]

    Thanks for that Pedro, it's most useful to know. Perhaps you should
    write a tutorial? I am inspired, and once again, never thought of using
    this method. :)

    Daz Guest

Similar Threads

  1. database design and asp question
    By Michael in forum Dreamweaver AppDev
    Replies: 2
    Last Post: June 27th, 09:23 AM
  2. Database Design Question !!
    By Adam Knight in forum ASP Database
    Replies: 3
    Last Post: August 14th, 08:45 AM
  3. Database Design
    By Mohan in forum Oracle Server
    Replies: 10
    Last Post: July 15th, 10:05 PM
  4. Database Design
    By Sampangi in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 13th, 09:28 PM
  5. Database design help required
    By Lee Bevan in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 1st, 01:34 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139