db schema advice

[troytime]

What would be the best db schema for storing thousands, if not millions of items in a method that could accurately show exact aisle and shelf data in a physical store?

So if i were to have a db with EVERY item at my local walmart, whats the best way to record aisle and shelf location?

I know things are going to move.
Things will be added and removed.
There's no predetermined amount of shelves OR number of items/slots per shelf/aisle.

I was thinking of using the length of the aisle and converting to a metric unit.
Or possibly a numerical representation relating to the entire length (so .25 would be 1/4 of the way down the aisle)

any advice?

mysql will be my rdbms

 

[dandragonrage]

What I'd do (not guaranteed to be the best way):

Item table
------------
itemid serial primary key
itemname varchar indexed
itemdescription varchar or text or whatever

Locations table
-----------------
locid serial primary key
locname varchar
locdescription varchar

Itemlocations
----------------
itemid foreign key
locid foreign key


You could expand it so that the xref table (itemlocations) puts items on shelves and then have another table that put shelves in another area, etc... But I think what I have above is fine.

 

[Markbnj]

You'd have to say more about what "exact aisle and shelf data" means. How accurate does the location data need to be? How accurately do you need to represent the shelves?

 

[EagleKeeper]

Aisles are coded.
Column sections are coded per aisle.
Shelves are then coded per column sections and aisle.

So you end up with a 3 dimenstion rubic type cube.
Each cubbyhole contains the product.

The size of the cubbyhole depends on your definition and how you will use it.

 

[troytime]

Originally posted by: Markbnj
You'd have to say more about what "exact aisle and shelf data" means. How accurate does the location data need to be? How accurately do you need to represent the shelves?

I need the data accurate enough to draw a graphical representation. (both as a top view aisle/store map, and as a shopper perspective view)

 

[EagleKeeper]

Originally posted by: troytime

Originally posted by: Markbnj
You'd have to say more about what "exact aisle and shelf data" means. How accurate does the location data need to be? How accurately do you need to represent the shelves?

I need the data accurate enough to draw a graphical representation. (both as a top view aisle/store map, and as a shopper perspective view)

If you define the physical sizes/locations of the columns and aisles, you would have no problem drawing a 2D diagram.

If they are not consistent, then for each aisle & column, you should have a set of fields for coordinate info.

 

[Markbnj]

<div class="FTQUOTE"><begin quote>Originally posted by: troytime
<div class="FTQUOTE"><begin quote>Originally posted by: Markbnj
You'd have to say more about what "exact aisle and shelf data" means. How accurate does the location data need to be? How accurately do you need to represent the shelves?</end quote></div>

I need the data accurate enough to draw a graphical representation. (both as a top view aisle/store map, and as a shopper perspective view)</end quote></div>

You're basically talking spatial coordinate data at that point. Do you also need to have the sizes, shapes, and appearance of the products? Or will you use icons?

Leaving the rendering part of it out for the moment (and it may have a big impact), is the shelving standardized? In other words, can you define a coordinate system for the store with an origin point (x and z axes), and then have a value for shelf number, knowing that shelf number 3 should be located a certain distance up the Y axis. From my experience in stores this doesn't always hold true.

Perhaps you should start by defining the shelving types. A certain unit of shelving is so long, and so high and wide, and has so many shelves at these heights and dimensions.

Then you could have aisles that contain a certain number of shelving units, and attach the individual products to shelves on a unit, and x,z locations on a shelf. Btw, I use x,z because you mentioned a perspective view. In a 3D world up is typically +y, right is +x, and in is +z. So you could get (x,z) from the store coordinates + the aisle coordinates + the shelving unit coordinates, and then get (y) from the shelving unit shelf height value for the shelf the item is on.

So the ER might look something like this...

Store (1..1) - (1..n) Aisle (1..1) - (1..n) ShelvingUnit (1..1) - (1..n) Shelf (0..n) - (0..n) Item

 

[troytime]

the shelving isn't standardized at all, nor are the aisle lengths or heights.

I think i MAY just end up going with a fuzzy location estimate and rely on text descripters.

At this point, making the system scalable enough to cover everything would exponentially increase development time

Thanks for everyone's brain!

 

[EagleKeeper]

<div class="FTQUOTE"><begin quote>Originally posted by: troytime
the shelving isn't standardized at all, nor are the aisle lengths or heights.

I think i MAY just end up going with a fuzzy location estimate and rely on text descripters.

At this point, making the system scalable enough to cover everything would exponentially increase development time

Thanks for everyone's brain!</end quote></div>

With all the variations; you will have to put the dimensions of each shelf, aisle and coordinates into the database as an unique entry. Link the product to the shelf location, the shelf to the column/aisle.

Generate map drawings will be a bitch, but you will have all the information needed.