Sample Clothing or Shoe store Database !!! (1 Viewer)

[fatboy123]

Hello and thanks in advance for your time, I've been looking EVERYWHERE for a simple free DB sample so that I can study how people design a DB for a RETAIL store which needs a MATRIX tables such as shoe stores where you have this kind of product tree:

The Shoe Name > which will have different styles > and each style will have different colors > and each color will have different sizes > located in different stores.

So a typical entry will be like this...

Name > Style > Color > Size > Qty > Price > Store Location
PUMA > 1234 > Black > 44 > 6 > 29.95 > XXX Mall

I've even looked at all table designs here...
http://www.databaseanswers.org/data_models/index.htm

And couldn't find any for a retail store, could anyone please direct me to any site with a free sample DB with such a design?

I appreciate all the info I can get.

Bobak.

 

[pr2-eugin]

I was about to refer to the same site, not sure if you have looked in properly.. They have some different names..

www.databaseanswers.org/data_models/retail_data_models.htm
http://www.databaseanswers.org/data_models/shoppers_recommendations/index.htm
http://www.databaseanswers.org/data_models/retail_customers/index.htm

 

[GinaWhipp]

Seems to me you're looking more for Inventory than Retail Store Shopping. Have you looked at the Inventory models?

 

[fatboy123]

Thank you so much gentleman for your kind infos, I had a look at all the links again and I'm still scratching my head, This one had a little bit of similarities but I still could not design my table structure for my shoe store...
http://www.databaseanswers.org/data_models/tk_maxx_product_catalogue/index.htm

Here's a typical product tree I made for simplification...

And here's a simple form which would display from that table...

Forgive me for wasting your times, but I've never developed this much complicated Matrix tables before, any help on table structure again appreciated.

Bobak.

 

[GinaWhipp]

Will this only be shoes or will there be other products involved?

 

[fatboy123]

Only Shoes, there'll be hundreds and hundreds of different models, colors and sizes, but all the same tree structure as above, I jotted down many different table designs but none of them made sense

 

[GinaWhipp]

Okay, I will come up with something after I get some sleep! (Almost 3:30 AM here...)

 

[fatboy123]

Thanks so much Gina, fingers crossed, have a good sleep

 

[GinaWhipp]

I think this should get you started...

 

[GinaWhipp]

fatboy123, I hope you are still watching this thread!

I wasn't sure if you would get an eMail when I updated the file. So, after some thought, I realized I needed to change some relationships and I added a few more tables. Have fun!

 

[fatboy123]

Gina,

God Bless, I really don't know how to thank you, I'm not all that good, and the Master table designs you've made for me has confused AND enlightened me quiet a bit on table design, if you don't mind giving me 2 or 3 days to wrap my head around the structure and understand them and come up with some follow up questions.

I thank you sincerely again, especially for the redesign

Bobak.

 

[GinaWhipp]

No problem, I kind of rushed the first one and it gave me nightmares! And... I have added that to my collection of Data Models. There are some tips at the bottom that might help clear your head!

http://www.access-diva.com/dm8.html

 

[fatboy123]

That's super, you have an amazing site, Going to spend some quality reading time there

 

[GinaWhipp]

Thanks... sent you back a message!

 

[MikeS122959]

Gina:
Your Shoes database looks terrific!! Our company sells styles in various widths (AAAA to EEEE) and each Style/Width/Size/Color combination has its own barcode. Could you help me understand the best way to address these additional requirements in the Shoes database?

Thanks,

MikeS122959

 

[GinaWhipp]

That adds a new wrinkle... Let me give that a little thought and I'm a bit busy right. I'll get back to you, shortly I hope. (Day job getting in the way!)

 

[GinaWhipp]

I think the best way would be to concatenate the Bar Code, that way you still have a normalized database and you have Bar Codes for every variation.

 

[MikeS122959]

Gina:
Thanks for the quick response.

If I understand correctly, we would store Style #, Width, Color, Size, & Barcode directly in one table, so there's zero chance of a mismatch. Is this correct?

Thanks,

Mike

 

[GinaWhipp]

No, you would store that data in their separate prospective tables and then create a query that joins them together thereby creating the Bar Code.

 

[MikeS122959]

Gina:
Thanks for the feedback. I'm a little out of date, so I had to learn what a normalized database was.

I apologize for not mentioning that we do not create the barcode. The manufacturer labels their product with a UPC-A barcode. upcitemdb.com/upc/745195325704 is for a pair of Allen Edmonds Style: 1635 (Men's Strand Cap Toe With Perfing), Color:Walnut, Sz: 9.5 US, Width: D. The UPC-A barcode associated with this style/size/color/width would be 7 45195 32570 4 (first & last digits are check digits & would not be included in the database). Every retailer receiving this style/color/width/size would use the same barcode to receive, inventory, and sell this product.

Thanks again for any suggestions you might have!!