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

Peter H. Coffin wrote:
> On 11 Mar 2007 09:32:51 -0700, LR120@LYCOS.COM wrote:
> > 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.
>
> 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.

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.