Professional Web Applications Themes

Table design question - MySQL

I am attempting to design a database for my group of companies scattered around the city that manage storage units to rent. The individual companies vary in size tiny to large. The tiny ones simply manage their units by number -- 1, 2,... The huge ones manage their units by specifying a 'lot', 'section', and finally a unit, where the unit exists in a 'section', a section exists in a 'lot', and a lot is a piece of the property. Its a lot like Michigan State campgrounds, now that I think of it... For the larger facilities, units might have ...

  1. #1

    Default Table design question

    I am attempting to design a database for my group of companies
    scattered around the city that manage storage units to rent. The
    individual companies vary in size tiny to large. The tiny ones simply
    manage their units by number -- 1, 2,... The huge ones manage their
    units by specifying a 'lot', 'section', and finally a unit, where the
    unit exists in a 'section', a section exists in a 'lot', and a lot is
    a piece of the property.

    Its a lot like Michigan State campgrounds, now that I think of it...

    For the larger facilities, units might have 3 layers between the
    'unit' and the total property. But the smaller ones might just have
    1, after all they only have 19 units to rent, no sense making it
    complicated. The medium sized places might have 2.

    To make matters weird, each facility names the levels differently.
    Facility 1 might have 3 levels - 'red', '1', 'N' while facility 2
    might also have 3 levels but they name them 'east', 'A', 'X'.

    So my question is; could anyone give some pointers on how to design
    the 'inventory' side of this where unit is the inventory which belongs
    to between 1 and say 3 different sub-levels?

    Its kind of like an inventory management system I worked on last year
    but instead of an item belonging to only 1 location which in turn
    belongs to 1 facility, I have an item belonging to 1 location but
    there are 1-n tiers between that location and the top facility.

    It came about when I was thinking about getting a 'list of units' and
    where they belong -- in some places it is 'unit 1 belongs to section A
    which is in lot BLUE but in other places it is just "unit 1, behind me
    with the big 1 on the door...'

    Thank you in advance for any suggestions or comments. I hope I'm
    making it more complicated than it needs to be.

    -Robert

    LR120@LYCOS.COM Guest

  2. #2

    Default Re: Table design question

    On 11 Mar 2007 09:32:51 -0700, COM wrote: 

    To the database, is there any REASON to differentiate? That is, is a
    particular unit of a class that exists across all sites and all that's
    changing is just the NAME of the paricular unit (not the class)? If so,
    what does it matter that one names its units "red-east-04", another
    "A2", and a third names them all after West Side Story starts like
    "Rita" and "Natalie"? It's all a varchar on a row with an internal int
    record number, and an int that's a foreign key to the table of sites.
    After all, the name isn't going to relevant to anything unless you're
    *at the site*, and looking for the unit.


    --
    When all you have is a hammer, every problem looks like a messiah.
    Peter Guest

  3. #3

    Default Re: Table design question

    Peter H. Coffin wrote: 
    >
    > To the database, is there any REASON to differentiate? That is, is a
    > particular unit of a class that exists across all sites and all that's
    > changing is just the NAME of the paricular unit (not the class)? If so,
    > what does it matter that one names its units "red-east-04", another
    > "A2", and a third names them all after West Side Story starts like
    > "Rita" and "Natalie"? It's all a varchar on a row with an internal int
    > record number, and an int that's a foreign key to the table of sites.
    > After all, the name isn't going to relevant to anything unless you're
    > *at the site*, and looking for the unit.
    >
    >
    > --
    > When all you have is a hammer, every problem looks like a messiah.[/ref]

    Thanks Peter for responding.

    At the moment, yes, I do need to differentiate. I have some legacy
    home-grown Access databases that will need to be migrated into this
    project's company-wide system. And, the consumers of the database are
    used to it, used to the reports they've generated (list of units by
    lot, and/or by section, etc.) and so on. I could do all that by
    parsing the varchar as you mention but I was hoping for a design
    solution rather than coding it. It just seems 'prettier' to me... If
    I fail to come up with table designs that work, I'll definitely do it
    this way!

    Thank you for your comments, I appreciate your time.

    LR120@LYCOS.COM Guest

  4. #4

    Default Re: Table design question

    On 12 Mar 2007 09:06:22 -0700, COM wrote: 

    Okeydoke. One thing to remember when making these kinds of choices and
    compromises is that most things that you can select for you can also
    make a view for, and for reporting purposes, a view and a table are
    generally interchangable. Another thing to remember is that "null" is
    a perfectly reasonable value for a GROUP BY column...

    --
    When all you have is a hammer, every problem looks like a messiah.
    Peter Guest

Similar Threads

  1. Efficient Table Design
    By capik79 in forum Coldfusion Database Access
    Replies: 2
    Last Post: June 12th, 01:46 PM
  2. Table Design Question
    By Paul in forum Informix
    Replies: 6
    Last Post: January 26th, 04:57 PM
  3. Newbie Database table design question (guid)
    By William in forum Microsoft SQL / MS SQL Server
    Replies: 10
    Last Post: August 1st, 10:35 PM
  4. Table design
    By Shamim in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 2nd, 04:28 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