Setting up tables & relationships - SQL Noob

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Setting up tables & relationships - SQL Noob

Author

Topic

BenWalters
Starting Member

2 Posts

Posted - 2014-03-12 : 18:04:00

Hi Everyone,

I am new to SQL and databases in general so be gentle.
I am creating a small piece of software which handles a pre-advice data file, several scanner inputting weights and a supplier weight report.

The pre-advice contains a list of barcodes and details revolving around that barcode, each barcode will be scanned and a weight will be obtained, this will be place in the DB.

Currently my tables can be summarised like this.

Pre-Advice Table
Barcode
Lots of other info…

Scanners Table
Barcode
Date
Time
Weight
Which device input this information

Suppliers Weight Table
Barcode
Date
Time
Weight

Here is the process(es):
1. Pre-advice table populated.
2. When the barcode is scanned and a weight is obtained, a record will be added to the scanners table and this becomes the ‘active’ weight.
******** If there is already an ‘active’ weight against this barcode the user will be asked if they wish to use the ‘active’ weight already in the system or update the ‘active’ weight to the weight they have entered.
3. Then when the supplier weight report is inputted into its table, should the ‘active’ weight be less than the supplier weight, the supplier weight becomes the ‘active’ weight.

At any given time a user can pull a report based on information in the Pre-Advice table, which will need to include the ‘active’ weight for that barcode’s record.

Additionally, no weights should be deleted, I need a fully traceable log of who, what, where & when. I will need to pull reports on the trials of scans so it really is a must to keep these.

Furthermore, if a scanner was to scan the same barcode after the Suppliers weight had been input and the user chooses to replace the weight then, the scanners weight should become the new ‘active’ weight.

I believe I will need to modify my tables to accommodate my needs, however I am not sure exactly what or how to set up what I need.
I understand I will need some type of relationship between all of the tables to be able to pull the report on the Pre-Advice table, but I am lost.

Please can anyone advise how I will need to set up my tables/relationships in order to achieve my requirements?

Any help/suggestions will be appreciated!
Please let me know if anything that is not clear or if you have any questions.

Many thanks in advanced.
Ben

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-03-12 : 20:06:08

Given the scanners table and suppliers weight table are almost identical, I would consider merging the two tables into one, and save supplier identifier in same field as the scanner identifier.
To have complete log of all weights at any given time, add field "active weight" to hold the chosen active weight.

BenWalters
Starting Member

2 Posts

Posted - 2014-03-12 : 20:24:50

quote:
Originally posted by bitsmed

Given the scanners table and suppliers weight table are almost identical, I would consider merging the two tables into one, and save supplier identifier in same field as the scanner identifier.
To have complete log of all weights at any given time, add field "active weight" to hold the chosen active weight.

Hi Bitsmed,

Thanks for your response!
I have thought of doing that or at least something similar! The problem is the suppliers report may be different depending on who gives us the information, i.e. there may be multiple suppliers sending us different reports. I would like to maintain all info sent for historical references. For example there are some suppliers who provide much more information than what I listed in the above table (sorry bad example from me)

Does SQL have a 'virtual' type of table, whereby it can take fields from multiple tables to make a virtual table where all of the data is in the same format? I am not sure what this would be called if it did even exist!

Else, do you see any other way of achieving this?

Thanks Again!

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-03-12 : 20:46:35

For virtual type of tables, you should look into views.
You could also make subqueries and have outer query "belive" the subquery to be one table, like this:

select *
  from (select barcode
              ,[date]
              ,[time]
              ,weight
          from [scanners table]
        union all
        select barcode
              ,[date]
              ,[time]
              ,weight
          from [suppliers weight table]
       ) as a
 order by a.barcode
         ,a.[date]
         ,a.[time]

but this doesn't solve situation where weight is scanned several times, and user decides if the newly scanned weight, is the active weight. As I read your requirements, all weights must be saved (even if a scanned weight is not used as "active weight"). This can be solved by adding a field (active weight) to scanners table.

edit: spell correction

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources