473,320 Members | 1,838 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Creating a better database

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.
Attached Images
File Type: jpg Capture.jpg (27.6 KB, 125 views)
Jun 26 '14 #1

✓ answered by twinnyfo

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

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

Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 1 tblVendorItems.Vendor, 
  2. tblVendorItems.VendorItem
  3. FROM tblVendorItems
  4. WHERE tblVendorItems.SKU="858" AND 
  5. tblVendorItems.CanShipSameDay=True AND 
  6. tblVendorItems.VendorPriority<>0
  7. 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:

Expand|Select|Wrap|Line Numbers
  1. Vendor   VendorITem
  2. 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!

4 1097
twinnyfo
3,653 Expert Mod 2GB
Jrod,

Your pic helps me understand your problem much better.

Table tblVendorItems:

Expand|Select|Wrap|Line Numbers
  1. Field           Description
  2. VendorItemID    Primary Key
  3. SKU             FK to tblSKUTiems
  4. Vendor          FK to tblVendors
  5. VendorPriority  You set this to determine which vendor you go with first
  6. VendorItem      Same as you currently have
  7. 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.
Jun 26 '14 #2
Thank you so much twinnyfo
Jun 26 '14 #3
twinnyfo
3,653 Expert Mod 2GB
Here is what your table will ultimately look like (NOTE: I've abbreviated the Field names to save space):

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

Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 1 tblVendorItems.Vendor, 
  2. tblVendorItems.VendorItem
  3. FROM tblVendorItems
  4. WHERE tblVendorItems.SKU="858" AND 
  5. tblVendorItems.CanShipSameDay=True AND 
  6. tblVendorItems.VendorPriority<>0
  7. 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:

Expand|Select|Wrap|Line Numbers
  1. Vendor   VendorITem
  2. 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!
Jun 26 '14 #4
Thanks twinnyfo, you've been a lot of help.
Jun 26 '14 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Dim St Thomas | last post by:
I am a developer working on a database client program. I am testing this program on a Windows XP machine (1.5 GHz AMD chip, 480 Mb RAM, 60 Gb disk) This machine has Oracle 9.2.0.1.0 and RedBrick...
0
by: Jonathan | last post by:
In the organisation where I work we currently have a public sanitised version of our existing HR personnel database. This is very widely used as the main source of employee info by many internal...
5
by: serge | last post by:
What is the best way to run one command and have a database be created and sql scripts run on it to create the tables, indexes, triggers, procedures, etc.? Is there an existing tool free or...
3
by: serge | last post by:
I have all the scrips to create a database. I have a few questions: 1- I am creating a batch file that it will call many lines like: db2 -td@ -f filename.sql -z output.txt The order i am using...
3
by: ElizaBlue | last post by:
Newbie here, I know a enough about MS Access to create a database. But can someone tell me how to get started. I have a project to create an automated database based on an existing word document....
1
by: Shelby | last post by:
Problem: My company generates its own data export from a propietary database. These (free) tables can be read in C#.NET using a Visual FoxPro driver (vfpoledb). I can read each of the six tables...
6
by: Jerry Spence1 | last post by:
Why doesn't the following work in my ASP program? I have imported ADOX I am trying to create a temporary database on the user's PC. The example is taken from Microsoft. Dim cat As Catalog =...
1
by: dave | last post by:
Hello: In Visual Studio 6.0 there is "VisData" to create Access database files. (file.MDB". Q: Does Visual Studio 2005 have tool(s) that create Access database files (file.MDB) ? thank...
1
by: Dan Rolfe | last post by:
Ok I just kinda stumbled upon an error I made when I was building this database. It is a multitable relational DB consisting of 2 levels of grouping. Here is a rough design view: Owner...
2
by: astolpho | last post by:
I am using a slightly outdated reference book on J2EE programming. It gives 2 methods of creating a database used in its casestudies. The first is an ANT script that gives the following output: ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.