ASP.NET, SQL Server, WPF, C#, VB.NET

i'm trying to set up a database tracking order history and order 
 
receipts for inventory items, but i'm not sure what should be in each 
 
table, what should be in the link table, and what kind of key should be 
 
used in each table. 
 
each item is identified by either IJ number or IA number. not all items 
 
have both numbers, but all have at least one. some parts are ordered 
 
under IA number and received under IJ number, or vice versa. some items 
 
are also referred to by multiple IJ or IA numbers, but i think i will 
 
ignore that for now as i'm confused enough already. i would also like 
 
to keep track of obsolete item numbers so they can be cross referenced 
 
to a valid current item number 
 
each item has multiple orders, each order contains multiple items. 
 
unique order lines can be identified by an IA/IJ number + an order 
 
number. date and quantity also needs to be recorded. 
 
each unique order line can be received on more than one invoice, and 
 
each invoice can contain the same order or item number (but not 
 
order+item number) multiple times. quantity received and invoice date 
 
is also recorded on this table. 
 
i think i need to use compound keys as identifiers, but i haven't been 
 
able to find much information on how they work in previous versions of 
 
access, and none about using them in access 2003. i have a lot of 
 
repeated information, but i can't figure out how to remove any of it 
 
without losing identifying information. 
 
here's my first stab at setting up tables, with possible compound keys 
 
in brackets. all matching column names would be linked across tables: 
 
[IANumber IJNumber] - link table. contains only part numbers. 
 
[IANumber IJNumber Order] OrderDate OrderQty - order table. 
 
[IANumber IJNumber Order Invoice] InvDate InvQty - receipts table. 
 
this is my first time setting up a database, so i'm not sure i'm going 
 
about it the most logical way. do i need more (or fewer) tables? is 
 
there an easier way to set up the tables or the keys? have compound 
 
keys been replaced by some other kind of key? 
 
any websites or books i should take a look at?

In a post on 8/8/06, Subject: Problem designing tables: Too many fields 
 
I saw where John Vinson posted this link: 
 
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html 
 
Looks like some good ones. 
 
Josh

Presumably the IJ number alone uniquely identifies the product (if it has an 
 
IJ), and so would an IA (if the product had one.) Therefore IJ and IA are 
 
merely aliases for the product. In your database, consistently use the one 
 
thing that *you* want to use as a unique identifier for the product - an 
 
Autonumber if you wish. Then plug in whatever names someone else needs in 
 
the context where you have to use it (such as on a report for ordering the 
 
product.) 
 
ProdCodeType table (one record for each alias, e.g. "IA", "IJ"): 
 
ProdCodeTypeID  Text             primary key 
 
Product table: 
 
ProductID            AutoNumber    primary key 
 
ProductName      Text 
 
ProductCode table: 
 
ProdCode            Text   The actual value of the IJ/IA code for this 
 
product. 
 
ProdCodeTypeID  Text   Relates to ProdCodeType.ProdCodeTypeID 
 
ProductID            Number   Relates to Product.ProductID 
 
For primary key, use ProdCode + ProdCodeTypeID, so the same combination 
 
cannot occur twice. 
 
Example data: 
 
ProductID    ProdCodeTypeID    ProdCode 
 
1                    IA               999xy 
 
1                    IJ                abcde 
 
2                    IA               987zz 
 
-- 
 
Allen Browne - Microsoft MVP.  Perth, Western Australia. 
 
Tips for Access users - http://allenbrowne.com/tips.html 
 
Reply to group, rather than allenbrowne at mvps dot org. 
 
<shadowsong@gmail.com> wrote in message 
 
news:1155766124.493257.26260@m79g2000cwm.googlegroups.com...

If I wanted to run a query that would give me all open orders for a 
 
particular item number, but put in a number other than the one set as 
 
the item ID, would it still give me results? For example, say that for 
 
one item, I have these numbers: 
 
98412773 - IA number, item ID 
 
98411025 - IA number, obsolete 
 
0004000704 - IJ number 
 
Our system uses the IA number, but when we receive orders, they have 
 
the IJ number, so when putting a new entry in the order receipt table, 
 
we would only have the IJ number available. New orders will be entered 
 
under the current IA number, but old orders may already be in the 
 
database under the obsolete number. How do I link all of these numbers 
 
together so that I can search for one number and return all records 
 
containing any of the three? 
 
Note that item numbers (and thus our unique identifiers) get obsoleted 
 
and replaced on a fairly regular basis - i would like to change the 
 
numbers on the link table without having to change all of the entries 
 
in the other tables.