Creating a better database

[Jrod2541]

I have this table:
image of my table
That I need split up into three separate tables.

One table for all the vendors (which I've made), though I only have 4 vendors at the moment: MDTV, LOWE, RYUU and GVHH.

One table for all the SKU ITEM #s (I've also made)

And lastly one table for the Vendor item #s.



That last table is tricky because I'm trying to make my database expandable (200+ different vendors), so I can't have separate fields for each vendor.

However, the vendor item #s must match the sku item #s.

for example on line 1 the [SKU ITEM #] is 123, the vendor named "MDTV" also has an item # on line 1 which is 4987.

I have another vendor named "RYUU" that also has a item # of 464.

The other two vendors don't have item #s on that line.



If I make a separate table containing just vendor item #s, keep in mind that vendors should NOT have their own separate tables, how do I keep that link between the SKU item # and the vendor item #s?

Any suggestions on what I can do?

Should I choose a different design other than the three tables?


Ultimately, I need to have the database automatically look at whatever is in the vendor 1 field, check if it can ship that day (if not look if vendor 2 can ship and use that, if not that check vendor3 and so on), and add the order to the VendorOrder field.

I'm far from the coding step though, right now I need to make a better database.

[twinnyfo]

Jrod,

Your pic helps me understand your problem much better.

Table tblVendorItems:

[B][U]Field           Description[/U][/B]
VendorItemID    Primary Key
SKU             FK to tblSKUTiems
Vendor          FK to tblVendors
VendorPriority  You set this to determine which vendor you go with first
VendorItem      Same as you currently have
CanShipSameDay  Yes/No checkbox

This should get you in the right direction.

I'll try to post what the table would look like based on your pic in just a while.

[Jrod2541]

Thank you so much twinnyfo

[twinnyfo]

Here is what your table will ultimately look like (NOTE: I've abbreviated the Field names to save space):

[B][U]ID   SKU   Vend  Pri  VItem   SameDay[/U][/B]
1    123   MDTV   2   4987    Yes
2    123   RYUU   1   464     Yes
3    124   MDTV   2   5001    Yes
4    124   RYUU   1   463     Yes
5    254   MDTV   2   3998    Yes
6    254   RYUU   1   085     Yes
7    542   MDTV   2   5000    Yes
8    542   RYUU   1   357     Yes
9    324   MDTV   2   4304    Yes
10   324   RYUU   1   874     Yes
11   786   MDTV   2   4387    Yes
12   786   RYUU   1   919     Yes
13   475   MDTV   2   7653    Yes
14   475   RYUU   1   898     Yes
15   521   MDTV   2   4389    Yes
16   521   RYUU   1   078     Yes
17   354   MDTV   2   4020    Yes
18   354   RYUU   1   489     Yes
19   254   MDTV   2   4300    Yes
20   254   RYUU   1   167     Yes
21   128   MDTV   2   4760    Yes
22   128   RYUU   1   168     Yes
23   009   MDTV   2   7542    Yes
24   009   RYUU   1   597     No
25   010   MDTV   2   9677    Yes
26   010   RYUU   1   598     Yes
27   704   MDTV   0   4555    Yes
28   704   LOWE   2   23-OH   No
29   704   RYUU   3   101     Yes
30   704   GVHH   1   804521  Yes
31   157   MDTV   0   1700    Yes
32   157   RYUU   1   416     Yes
33   157   GVHH   2   830921  Yes
34   858   MDTV   0   4044    Yes
35   858   LOWE   0   23-HF   Yes
36   858   RYUU   1   413     Yes
37   858   GVHH   2   207871  Yes
38   821   MDTV   0   4817    Yes
39   821   LOWE   0   23-HJ   Yes
40   821   RYUU   1   132     Yes
41   821   GVHH   2   507702  Yes
42   724   MDTV   0   8978    Yes
43   724   LOWE   0   23-IU   Yes
44   724   RYUU   1   533     Yes
45   724   GVHH   2   434501  Yes
46   428   MDTV   0   4567    Yes
47   428   LOWE   3   23-YU   Yes
48   428   RYUU   1   471     No
49   428   GVHH   2   459601  Yes
50   129   MDTV   0   5287    Yes
51   129   LOWE   0   23-FF   Yes
52   129   RYUU   1   503     Yes
53   129   GVHH   2   784701  Yes
54   219   MDTV   0   5647    Yes
55   219   LOWE   0   42-SD   Yes
56   219   RYUU   1   219     Yes
57   481   MDTV   0   4456    Yes
58   481   LOWE   0   42-BG   Yes
59   481   RYUU   1   925     Yes
60   484   MDTV   0   6547    Yes
61   484   LOWE   0   42-BB   Yes
62   484   RYUU   1   914     Yes
63   411   MDTV   0   6457    Yes
64   411   LOWE   0   46-EH   Yes
65   411   RYUU   1   470     Yes
66   442   MDTV   0   4564    Yes
67   442   LOWE   0   31-TF   Yes
68   442   RYUU   1   488     Yes
69   754   MDTV   0   4898    Yes
70   754   LOWE   0   31-AS   Yes
71   754   RYUU   1   987     Yes
72   321   MDTV   0   4567    Yes
73   321   LOWE   0   31-MN   Yes
74   321   RYUU   1   949     Yes

Please excuse any typos......

In general, you will notice that your table is now much taller than before, but it is much easier to deal with tall tables than wide tables. This actually takes us less space as a table, because you don't have wasted space allocated for fields in records that are blank.

ALSO NOTE: (high emphasis for a reason!) Your SKUs are in TEXT format, because of the leading zeroes in some cases. Also, because the Vendor Items are a mixture of numbers for some vendors and text and numbers for others, you must make sure to set the data type to Text, and that the rules for that field will work for all vendors.

So, how does this work? Based on the data you provided, it looks like some of the vendors are not preferred at all for some items. So, those vendors who carry that item, but have no preference become a zero. You can change these values whenever.

Now, lets say I want to ship item 858 (your SKU), which is on the table under IDs 34-37 (just for reference). You want to find the prefered vendor that ships the same day:

SELECT TOP 1 tblVendorItems.Vendor, 
tblVendorItems.VendorItem
FROM tblVendorItems
WHERE tblVendorItems.SKU="858" AND 
tblVendorItems.CanShipSameDay=True AND 
tblVendorItems.VendorPriority<>0
ORDER BY tblVendorItems.VendorPriority;

We are just selecting the first record in this case ("TOP 1"), and since we are sorting by the Priority, the lowest number gets picked first. However, with "0" as the priority for those non-preferred vendors, we then must exclude those rcords.

The Query above will return:

[B][U]Vendor   VendorITem[/U][/B]
RYUU     413

Hope this helps you understand the direction we are taking you with this Table. This is a good theory behind the construction, and should be very useable, based on what you have provided us, thus far.

Let me know if this was useful for you!

[Jrod2541]

Thanks twinnyfo, you've been a lot of help.