UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Table Design, Access 2016    
 
   
davidgk
post Jan 27 2020, 11:49 PM
Post#1



Posts: 33
Joined: 27-May 15



HI
This is probably a basic question that I am having a hard time figuring out
I need to design a database that will keep track of amazon inventory each item has an sku # , but sometimes each item can have 2 or 3 sku # (all 3 sku’s have a unique parent number which represents all 3 sku’s)
I need to keep track of sales and purchases for both the parent, and for each sku as well
Also all items are part of a category (I guess that should be on separate table for sure)
How should I design these tables I will be importing my previous history from excel files
Any help , or link where I can download a similar access database ,will be greatly appreciated
David
Go to the top of the page
 
theDBguy
post Jan 28 2020, 01:52 AM
Post#2


UA Moderator
Posts: 77,727
Joined: 19-June 07
From: SunnySandyEggo


Hi David. Do all SKUs have a parent number even if only one SKU belongs to that parent number? Just curious...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
davidgk
post Jan 28 2020, 11:28 AM
Post#3



Posts: 33
Joined: 27-May 15



Yes
All sku's have a parent number (its text)
so a parent can have 1 child, or many children
basically sometimes i will need the total info of all children (parent) and sometimes by each child and sometimes bay category
the children are actually the same (sometimes they were packed differently so the price is not the same, but many times they are the same ) but in January they where called "abc" and in march they where called "def" and now they are called "nop" etc
Thanks
Go to the top of the page
 
orange999
post Jan 28 2020, 11:42 AM
Post#4



Posts: 2,080
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


davidgk,

Can you describe in simple English a brief scenario overviewing the sales and purchases of the things and how the skus interrelate and the name changes? I think readers need a more concrete example to appreciate the context and processes involved.
This post has been edited by orange999: Jan 28 2020, 11:42 AM

--------------------
Good luck with your project!
Go to the top of the page
 
davidgk
post Jan 28 2020, 12:14 PM
Post#5



Posts: 33
Joined: 27-May 15



Any new customer order or purchase order will be (should be) entered with the current sku
But would like to see the history (both in the order entry form , and in reports) for the current sku, as well as the related skus ie history of what was sold etc for this sku by itself , as well as history for this and all related skus combined (parent)
I hope I am clear with this
Thanks
Go to the top of the page
 
orange999
post Jan 28 2020, 01:29 PM
Post#6



Posts: 2,080
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


I don't know the details.
, but am attaching a draft for consideration.
It may give you a starting point.
Attached File(s)
Attached File  skudraft.PNG ( 26.83K )Number of downloads: 4
 

--------------------
Good luck with your project!
Go to the top of the page
 
projecttoday
post Jan 28 2020, 01:48 PM
Post#7


UtterAccess VIP
Posts: 11,809
Joined: 10-February 04
From: South Charleston, WV


It seems to me that the product table only needs the child sku since in the sku table child table it points to the sku parent table.

--------------------
Robert Crouser
Go to the top of the page
 
orange999
post Jan 28 2020, 02:31 PM
Post#8



Posts: 2,080
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


I agree -probably true Robert. I was proposing a draft model for davidgk -who, hopefully, would adjust/revise/post concerns to meet his needs.
I am a little confused where names change from time to time, and don't have any working knowledge of amazon inventory or the sku usage involved.

--------------------
Good luck with your project!
Go to the top of the page
 
davidgk
post Feb 2 2020, 12:48 PM
Post#9



Posts: 33
Joined: 27-May 15



Hi
Sorry for late response
I was unable to start this project will try to start today or tomorrow
I wonna do this right, so my question is about primary key on these tables
Should I use the fields that you showed (skuparent = skuPid , skuchild = skuCid , product = prodid , custthirdparty = custid use these as primary key ?
Or should I use a separate primary key ?
Also is it better to put “all “ transactions in 1 table ? both the sell and the purchase (will need help on creating the forms for these)
I will probably have more questions as im doing this ……
Thanks
Go to the top of the page
 
projecttoday
post Feb 2 2020, 03:01 PM
Post#10


UtterAccess VIP
Posts: 11,809
Joined: 10-February 04
From: South Charleston, WV


The recommended practice is to use a separate primary key. If any of the fields which you mentioned should exist only in one record then you create an index on the field and set unique = yes.

--------------------
Robert Crouser
Go to the top of the page
 
GroverParkGeorge
post Feb 2 2020, 03:33 PM
Post#11


UA Admin
Posts: 36,987
Joined: 20-June 02
From: Newcastle, WA


Perhaps you might get some additional insight by reading more about Surrogate and Natural Primary Keys.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    5th April 2020 - 02:12 AM