Sample Clothing Inventory Database

[silhouette]

Hello and thanks in advance for your time

I've been looking for a simple free DB sample so that I can study how people design a DB for a clothing store which needs matrix tables such as clothing outlets where you have this kind of product tree:

SKU / Product Number > Product Description / Name > Colour > Size > Quantity Available (on hand and/or held at supplier) > Supplier > MSRP > Price

So a typical entry will be like this...

Name > SKU > Color > Size > Qty > Price >
M&S > 1234 > Black > 16 > 6 > 3 on hand > 3 at supplier > supplier_x> MSRP £29.95 > £19.95

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.

 

[CJ_London]

I doubt you will find anything to exactly match your requirement but you could cobble together ideas based on a variety of templates - e.g. inventory management, logistics. You may find something around printing/embroidering clothing which has the requirement for sizing/colour of garments.

Suggest you look at a number of the access templates plus look at the northwind database for ideas. Also google 'normalisation' to learn how databases are designed.

Also google 'access templates' which will produce a number of links for free tmeplates, including this one

http://access-templates.com/

Also be aware that templates work off basic rules which often will not account for real
life situations. You will need to sit down with pen and paper to work out how your business actually works and the rules to be applied

 

[The_Doc_Man]

As to what CJ suggested, I strongly second the motion. Full-on agreement.

The advice I often give is simple, perhaps sometimes TOO simple - but it is a good rule that I will stand by for purposes of teaching.

If you can't do it on paper, you can't do it in Access. Expanding that a bit, it is like asking a carpenter who has never built a Victorian armoire to build one. Without good blueprints, you'll get an ordinary, boxy-looking, free-standing cabinet.

The starting point is NEVER Access - it is problem analysis to SIMULATE what you will want Access to track for you. My own favorite design method is to get a few packs of sticky note pads (which won't be a waste as you can use the leftovers in your business later) and a dry-erase board of some reasonable size. Then you start identifying the business entities you will be tracking.

For instance, you named products and suppliers, two rather obvious entities. You ALSO identified a wrinkle - that the same product might be available from two different suppliers, potentially at different prices, which leads to what we call a "Junction Table." Your problem will FIRST be to see explicitly how to lay out the records.

For the garment, you have an SKU, name, description, color, size, etc. You have to decide whether this is one or two tables. (I'm thinking at least TWO - the inventory entry with size, color, SKU, possibly a name, and maybe a style-number code; and a separate item-design number leading to a table that holds the description and other items based on the style-number code.) Having the multiplicity of suppliers isn't an issue if you have the Junction Table between garment and supplier, and it would tell you things like, supplier A only handles smaller sizes but supplier B handles every size we want to carry but is more expensive.

This is what we mean when we say you have to work out how your business actually wants to track these things. On the other hand, most of us will probably agree that looking at sample databases is a good way to get ideas. Just don't forget that those samples relate to someone else's problem, not yours. Get ideas from them - but the problem you want to solve is your own problem, and the samples will not do that. Keep that perspective.

 

[jdraw]

As has been said, I also do not think you'll find a free sample DB that meets your specific needs exactly.
Here is a link to an Stock Management tutorial that may give you some insight.

Here is another link (Barry Williams' site) re Customer and Inventory.
Attached is a very draft model I created for a similar request.

Good luck.

 

[silhouette]

Thank you all for your kind replies.

I can't seem to find a sample database with what I need - most inventory databases I have seen seem to have simple products - whereas my products come in different colours & different sizes - ie blue size 10, blue size 12, red size 12 - for the same product ID.

It's been a while since that last time I built an access database - most of my inventory has been kept in spreadsheets to date.

Getting my head around it all is proving a challenge :banghead:

 

[silhouette]

As to what CJ suggested, I strongly second the motion. Full-on agreement.

The advice I often give is simple, perhaps sometimes TOO simple - but it is a good rule that I will stand by for purposes of teaching.

If you can't do it on paper, you can't do it in Access. Expanding that a bit, it is like asking a carpenter who has never built a Victorian armoire to build one. Without good blueprints, you'll get an ordinary, boxy-looking, free-standing cabinet.

The starting point is NEVER Access - it is problem analysis to SIMULATE what you will want Access to track for you. My own favorite design method is to get a few packs of sticky note pads (which won't be a waste as you can use the leftovers in your business later) and a dry-erase board of some reasonable size. Then you start identifying the business entities you will be tracking.

For instance, you named products and suppliers, two rather obvious entities. You ALSO identified a wrinkle - that the same product might be available from two different suppliers, potentially at different prices, which leads to what we call a "Junction Table." Your problem will FIRST be to see explicitly how to lay out the records.

For the garment, you have an SKU, name, description, color, size, etc. You have to decide whether this is one or two tables. (I'm thinking at least TWO - the inventory entry with size, color, SKU, possibly a name, and maybe a style-number code; and a separate item-design number leading to a table that holds the description and other items based on the style-number code.) Having the multiplicity of suppliers isn't an issue if you have the Junction Table between garment and supplier, and it would tell you things like, supplier A only handles smaller sizes but supplier B handles every size we want to carry but is more expensive.

This is what we mean when we say you have to work out how your business actually wants to track these things. On the other hand, most of us will probably agree that looking at sample databases is a good way to get ideas. Just don't forget that those samples relate to someone else's problem, not yours. Get ideas from them - but the problem you want to solve is your own problem, and the samples will not do that. Keep that perspective.

Thank you Doc Man for your reply, which I found very useful indeed - I think my issue is that I have a good idea of what I want to do but lack the know how to create my vision.

But I taught myself to build websites in magento and am determined not to let Access get the better of me. Will do more research and then revisit I think.

 

[Gasman]

Might want to look at Northwind 2 mentioned here. Do a search.
I would expect a record for each permutation mentioned.

 

[blynne.didi]

Might want to look at Northwind 2 mentioned here. Do a search.
I would expect a record for each permutation mentioned.

Thanks for that, I hope that'll point me in the right direction
I'm using v2016 cuz my Access is v2016. Besides, re:the later version "NWind Starter Edition", I'm having a devil of a time trying delete the data or save as new without data for it . I don't know enough about Macros & VBA to manipulate it & delete some of the junk I don't need.

 

[jdraw]

Note: This thread is about 7 years old! Just sayin'.
Silhouette was last seen here Sept 2016..............................................................

Perhaps could/should be a new thread

 

[blynne.didi]

Note: This thread is about 7 years old! Just sayin'.
Silhouette was last seen here Sept 2016..............................................................

Perhaps could/should be a new thread

Thanks Jdraw. I can , but since this was my exact issue, I hoped perhaps someone would have advanced enough to get a solution. If I get more stuck,I will start anew.

 

[jdraw]

OK. Since it was your exact issue, then let's leave it here and see if the thread gets some posts/responses.
You can continue to post any ideas, sketches, descriptions, business processes etc and that should get some interest.

There are a variety of articles and links in the Database Planning and Design link in my signature. You might wish to review some of those to get some insight and/or direction.

Forum members usually respond to posts that show an honest effort, any further info you have should be presented.
Good luck.

 

[Cotswold]

I wrote a program for a clothing importer and wholesaling distributer specialising on oversize to huge. Not quite store operations but importing from suppliers and selling many at a time to stores, rather than one or sales at a time in a shop. But included full stock control, purchase and sales invoicing etc. They closed down about ten years go but if I come across the program in my archive I'll put a copy online. Stars Apparel they were called.

 

[blynne.didi]

I wrote a program for a clothing importer and wholesaling distributer specialising on oversize to huge. Not quite store operations but importing from suppliers and selling many at a time to stores, rather than one or sales at a time in a shop. But included full stock control, purchase and sales invoicing etc. They closed down about ten years go but if I come across the program in my archive I'll put a copy online. Stars Apparel they were called.

That would be fantastic. Our biz is also a wholesaling startup. Many pieces of many different styles of many sizes & colors

 

[Gasman]

I tried for a job with a clothing company that sold through Amazon.
The upload was a non normalised spreadsheet if I recall correctly.

One row for each product, but all the variations were in columns.

 

[blynne.didi]

I tried for a job with a clothing company that sold through Amazon.
The upload was a non normalised spreadsheet if I recall correctly.

One row for each product, but all the variations were in columns.

My excel invoice looks like this

 

[Gasman]

My excel invoice looks like this

It was not the invoice, but the data to upload?

 

[jdraw]

blynne.didi

What is the current status of your project? WE need to get some info on you and your environment.
Is it Excel or Access? Or some combination?
Do you have any experience with database?
Do you have a list of business processes along the lines of Suppliers, Products, Sales, Orders.... and the "things" involved in each?
Here is a design approach from a database site (Barry Williams) that is no longer online. It may help you.depending on you current status.

These are the Steps in a Top-Down Approach :-

1. Define the Scope as the Area of Interest,(e.g. the HR Department in an organization).
2. Define the "Things of Interest",(e.g. Employees), in the Area of Interest.
3. Analyze the Things of Interest and identify the corresponding Tables.
4. Consider cases of 'Inheritance', where there are general Entities and Specific Entities.
   For example, a Customer is a General Entity, and Commercial Customer and Personal Customer would be Specific Entities.
   If you are just starting out, I suggest that you postpone this level of analysis.
5. At this point, you can produce a List of Things of Interest.
6. Establish the relationships between the Tables.
   For example, "A Customer can place many Orders", and "A Product can be purchased many times and appear in many Orders."
7. Determine the characteristics of each Table,(e.g. an Employee has a Date-of-Birth).
8. Identify the Static and Reference Data, such as Country Codes or Customer Types.
9. Obtain a small set of Sample Data,
   e.g. "John Doe is a Maintenance Engineer and was born on 1st. August, 1965 and lives at 22 Woodland Street, New Haven.
   "He is currently assigned to maintenance of the Air-Conditioning and becomes available in 4 weeks time"
10. Review Code or Type Data which is (more or less) constant, which can be classified as Reference Data.
    For example, Currency or Country Codes. Where possible, use standard values, such as ISO Codes.
11. Look for 'has a' relationships. These can become Foreign Keys, or 'Parent-Child' relationships.
12. You need to define a Primary Key for all Tables.
    For Reference Tables, use the 'Code' as the Key, often with only one other field, which is the Description field.
    I recommend that names of Reference Data Tables all start with 'REF_'.
    For all non-Reference Data Tables, I suggest that you simply assign an Auto-increment Integer for each Primary Key.
    This has some benefits, for example, it provides flexibility, and it's really the only choice for a Database supporting a Web Site.
    However, it complicates life for developers, which have to use the natural key to join on, as well as the 'surrogate' key.
    It also makes it possible to postpone a thorough analysis of what the actual Primary Key should be, Which means, of course, that it often never gets done.
13. Confirm the first draft of the Database design against the Sample Data.
14. Review the Business Rules with Users,(if you can find any Users).
15. Obtain from the Users some representative enquiries for the Database,
    e.g. "How many Maintenance Engineers do we have on staff coming available in the next 4 weeks ?"
16. Review the Results of Steps 1) to 9) with appropriate people, such as Users, Managers,
    Development staff, etc. and repeat until the final Database design is reached.
17. Define User Scenarios and step through them with some sample data to check that that Database supports the required functionality.

 

[blynne.didi]

blynne.didi

What is the current status of your project? WE need to get some info on you and your environment.
Is it Excel or Access? Or some combination?
Do you have any experience with database?
Do you have a list of business processes along the lines of Suppliers, Products, Sales, Orders.... and the "things" involved in each?
Here is a design approach from a database site (Barry Williams) that is no longer online. It may help you.depending on you current status.

These are the Steps in a Top-Down Approach :-

1. Define the Scope as the Area of Interest,(e.g. the HR Department in an organization).
2. Define the "Things of Interest",(e.g. Employees), in the Area of Interest.
3. Analyze the Things of Interest and identify the corresponding Tables.
4. Consider cases of 'Inheritance', where there are general Entities and Specific Entities.
   For example, a Customer is a General Entity, and Commercial Customer and Personal Customer would be Specific Entities.
   If you are just starting out, I suggest that you postpone this level of analysis.
5. At this point, you can produce a List of Things of Interest.
6. Establish the relationships between the Tables.
   For example, "A Customer can place many Orders", and "A Product can be purchased many times and appear in many Orders."
7. Determine the characteristics of each Table,(e.g. an Employee has a Date-of-Birth).
8. Identify the Static and Reference Data, such as Country Codes or Customer Types.
9. Obtain a small set of Sample Data,
   e.g. "John Doe is a Maintenance Engineer and was born on 1st. August, 1965 and lives at 22 Woodland Street, New Haven.
   "He is currently assigned to maintenance of the Air-Conditioning and becomes available in 4 weeks time"
10. Review Code or Type Data which is (more or less) constant, which can be classified as Reference Data.
    For example, Currency or Country Codes. Where possible, use standard values, such as ISO Codes.
11. Look for 'has a' relationships. These can become Foreign Keys, or 'Parent-Child' relationships.
12. You need to define a Primary Key for all Tables.
    For Reference Tables, use the 'Code' as the Key, often with only one other field, which is the Description field.
    I recommend that names of Reference Data Tables all start with 'REF_'.
    For all non-Reference Data Tables, I suggest that you simply assign an Auto-increment Integer for each Primary Key.
    This has some benefits, for example, it provides flexibility, and it's really the only choice for a Database supporting a Web Site.
    However, it complicates life for developers, which have to use the natural key to join on, as well as the 'surrogate' key.
    It also makes it possible to postpone a thorough analysis of what the actual Primary Key should be, Which means, of course, that it often never gets done.
13. Confirm the first draft of the Database design against the Sample Data.
14. Review the Business Rules with Users,(if you can find any Users).
15. Obtain from the Users some representative enquiries for the Database,
    e.g. "How many Maintenance Engineers do we have on staff coming available in the next 4 weeks ?"
16. Review the Results of Steps 1) to 9) with appropriate people, such as Users, Managers,
    Development staff, etc. and repeat until the final Database design is reached.
17. Define User Scenarios and step through them with some sample data to check that that Database supports the required functionality.

I'm so so sorry folks, I have been a bit messy explaining myself. I thought I had it sorted out, but in the end, I don't. So once again from the top.

The excel example (attached) is the kind of data I need to re-create in Access (v2016) - I'm using created fresh database from Northwind (v2016) template as my starting point.

I've started this journey with the Products table. The template is made for a simple product with no variables. But in clothing you get color & size for each style. So I made separate tables each for Color and Sizes. And I have no trouble to Add & Link them to Products.

*However*, the issue is that as it is now, it appears that I have to enter one data line for each permutation of the style eg "cardigan, brown, sm; cardigan blue sm, cardigan brown medium, cardigan blue medium, etc" This, eventually, is going to grow to 1000's of permutations and a ton of tedious data entry hours.
Is there a less onerous way to get around it? Or, could all those permutations & data entry hours might be shortened with importing data from an excel?

Especially, in particular, I want to make the Order Details Form as friendly as possible - by making look similar to that excel invoice - ie a grid, one line for a product, and the colors & sizes on a grid that can be filled in with numbers, and can produce an Invoice (report) that looks something like this excel.

dear forum folks, I've used Access before so I'm familar with it, but I'm no expert. Can you help or advise?

 

[Cotswold]

Hello silhouette, I've attached the BE database for you and a PDF with screen shots. The program is somewhen older than I thought. Maybe the first write was in 2002 and was in Acces97. Screen sizes nothing like you'd expect today though. Hopefully the database will mean more to you, being day to day in the job. Hopefully helpful.

p.s. I'll have to send the database later, because the copy is too large. I'll chop out some records.

 

[Cotswold]

Here is the BE with less records.