Need help with database design

Discuss the database features
Post Reply
bongbang
Posts: 8
Joined: Tue May 23, 2017 2:08 am

Need help with database design

Post by bongbang »

This is my first database application. Although I've finished reading the detailed Base Tutorial: From Newbie to Advocate in a one, two... three!, I'm still not sure how to accomplish what I need to do.

My problem: My company uses one spreadsheet to keep track of our stock of devices, and another spreadsheet to log shipments of these devices. Every time I receive or make a shipment, I have to update two spreadsheets. Surely, these sheets should be combined into a single database, which can be updated in a single step. I want my application to record all of the shipments and allow me to see all devices currently in stock. I also want to be able to query a device and see all that's ever happened to it.

As a minimal example, I can imagine a schema like this.

DEVICES
* Device_ID (primary key)
* Purpose (demo/sale)

ACTIVITIES
* Activity_ID (primary key)
* Date
* Device_ID
* Action (ship/receive/repair)
* Notes

To find what devices I have in stock, I plan to query ACTIVITIES for all machines whose latest "receive" action is not matched by a later "ship."

My questions:
1. Is this a workable model for what I want? Is there a better way?
2. How do I actually write the abovementioned SQL query for devices in stock?
3. How should I modify this schema to also track complaints or problems with each machine (whether they're in my possession or customers'). Should create a new COMPLAINTS table (and form with activities as a sub-form) and add ComplaintID as a column to ACTIVITIES?

I am a little lost, but these should be simple questions for someone with more experience. Thank you.
LibreOffice 5.3.6.1. Windows 10. MySQL 6.3.
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Need help with database design

Post by UnklDonald418 »

1. Is this a workable model for what I want?
Yes, to me it appears to be workable.
Is there a better way?
No way to tell without more details on how you are planning on using the database.
2. How do I actually write the abovementioned SQL query for devices in stock?
Something like this might work.

Code: Select all

SELECT "Device_ID", MAX("Date") FROM "ACTIVITIES" WHERE "Action" = 'Received' GROUP BY "Device_ID"
This query may not work correctly if the received date and the shipped date are the same. One solution is to use a Timestamp rather than a simple date for the Date field.
If the COMPLAINTS table has a field for the Device_ID , you wouldn't need to add ComplaintID as a column to ACTIVITIES.
Actually, including the ComplaintID in the ACTIVITIES table would be a violation of the first normal form, because each Activity_ID for a particular Device_ID would need to duplicate that ComplaintID. Plus what you wouldn't be able to handle the situation if more than one complaint was logged against a particular device.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Need help with database design

Post by eremmel »

You have no amount field in your ACTIVITIES table, so each mutation is related to one device....
To get your balance you might do something like:

Code: Select all

SELECT "Device_ID"
 , SUM( CASE WHEN "Action" = 'Received' THEN 1 WHEN "Action" = 'Ship' THEN -1 ELSE 0 END ) AS "Stock"
FROM "ACTIVITIES" 
GROUP BY "Device_ID"
Note: this method wil become slower over time because you need to go over all saved activities.
Note: You have no other option to add "Action" = 'Received' when you initialize your Stock, it that wat you want?
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
bongbang
Posts: 8
Joined: Tue May 23, 2017 2:08 am

Re: Need help with database design

Post by bongbang »

Thank you for your input. I have decided that putting "ship" and "receive" in the generic Action table is a bad idea, since those actions require different fields from, say, repair actions. Besides, a shipment can contain more than one device. So I've decided to make a separate Shipment table and link it to the devices through an association table.

Shipments
=======
* shipment_id
* Ship(_receive?)_date
* Receiver(_sender?)
* Authorized_by
* Tracking_number
* Notes

Ship_Device
=======
* shipment_id
* device_id

My questions are as follows:
1. Should I put both ship and receive entries in one table and distinguish them by an additional field? My main concern is to make my stock query simple and efficient, and this seems to argue in favor of putting them together. However, I also care about whether this "makes sense" from a database design standpoint. Shipping and Receiving in fact don't have exactly the same informational requirements. The latter doesn't need the Authorized_by and Tracking_number fields, for example.
2. Even if I put shipping and receiving in a different table (or tables) from repair, there must be a simple way for me to query all the actions that ever happen to on a device, right? In other words, all "ship", "receive", and "repair" items associated with a device can be concatenated into a chronological table for viewing in a form or report.

Thank you.
LibreOffice 5.3.6.1. Windows 10. MySQL 6.3.
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Need help with database design

Post by UnklDonald418 »

I agree, using separate tables for ship and receive entries seems to make sense.
There are a number of tools available that can be used to assemble data from multiple tables “into a chronological table for viewing in a form or report”, including SQL Joins and Unions, Sub Forms and Views
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Post Reply