SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast Pure L's Avatar
    Join Date
    Oct 2007
    0 Post(s)
    0 Thread(s)

    mind cannot grasp simple db design....ugh. (mysql/php)

    I cannot figure out why this is so hard.

    Really shouldn't be and I am WELL aware of that.

    I need to build a database for documenting the belongings of residents of an adult family home.

    I think I'm over-thinking it due to being scared of creating some sort of update and/or delete anomaly.

    Basically, I need one table for the residents and then one table for their belongings and one table for the category of belongings, right?

    The categories would be "clothing, jewelry, assistive_devices, petty_cash" and "other".

    I would then like to make a text box to enter the description of the item into the "belongings" table. It would be nice if I could comma separate each entry so I could enter many at once. Something like:

    1 pair of blue tennis shoes, 1 pair of white tennis shoes
    .....and then check the "clothing" check box.

    Here's where I get lost though.........since there are many different types of clothing (shoes, socks, pjs, shirt, etc) shouldn't I make a clothing table too?

    So then checking a checkbox in the clothing table opens up another bunch of check-boxes (shoes, socks, pjs, shirts) too???

    All of this and, yet, every item has to belong to someone too?!

    Any advice is much appreciated. Thanks, all.

    If I could just figure out how to start this thing I'd be much happier.

  2. #2
    SitePoint Wizard
    Join Date
    Dec 2003
    29 Post(s)
    0 Thread(s)
    Well, you have a couple things going on, and a couple of choices.

    First, we'll start with the simple part, the list of residents. The table would look something like this:
    id - primary key and auto_increment
    name - You could split this up into first and last name if you'd like. This should be a varchar, probably of about 30 just to be safe, or 60 total if you just use name.
    ... any other values you want to keep on the residents.

    Then, you have a choice. If you want to break possessions down further, you can create more tables, but if you just want one categories table, it would look like this:
    id - primary key auto_increment
    category_name - The name of the category

    If you wanted to create sub-categories you could do it like this:
    id - primary key auto_increment
    parent_category_id - the ID that links it to the category table
    category_name - The name of the sub-category

    Now, for the possessions, you can do it like this:
    id - primary key auto_increment
    resident_id - ID of the owner of the item.
    category_id - ID of the category
    sub_category_id - ID of the sub-category. Alternately, if you are going to require them to have a sub_category for everything, you don't need the category_id, and that would likely be the best idea.
    quantity - The quantity of the item.
    description - Either BLOB or TEXT for this, where they can enter information.

    So, you'll have a list of categories and sub-categories. when you get a new resident, you will go and add a new row for them in the residents table. Then, you go and add each of their possessions in the possessions table.

    Here are how the tables link: = possessions.resident_id // Links the owner with their possession = sub-category.parent_id // Links the sub-category to it's parent category
    possessions.category_id = // Links the possession with the appropriate category.
    possessions.sub-category_id = // Links the possession with the appropriate sub-category. Like I stated earlier, if you can, require every item to have a sub-category instead of a category, then exclude the category_id field from the possessions id. That way, if you re-categorize things, you only change it in one place instead of two.

    Now, when you go to figure out what possessions a specific resident has, do this:
    SELECT * FROM `possessions` WHERE `resident_id` = $id
    To figure out all the sub-categories for a category, use something like this:
    SELECT `id`,`name` FROM `sub-categories` WHERE `category_id` = $id

    And so on.

    Hope that helps. If you have any more questions, let us know.


Posting Permissions

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