[AccessD] Fwd: FW: table design

[David McAfee]

Thanks Jim,

A "line" is a phone number.
Our company currently has roughly 1500 cell phone numbers spread among 6
verizon accounts numbers.

Originally I was advised to use the Phone number as a PK, and not wanting
to go off on the whole natural vs surrogate PK holy war, I should them
examples in their systems where phone numbers were missing or replaced with
notes.

Your second paragraph describing the 200 paid phone numbers (lines) is
correct.

I guess I don't really need to separate the accounts and lines (other than
for normalization reasons).


On Fri, Sep 25, 2020, 5:12 AM Jim Dettman via AccessD <
accessd at databaseadvisors.com> wrote:

> David,
>
>  I think your design is right on.   The only question is what does a
> "Line" represent?   If it’s a phone #, then it does not need to be a
> separate table.
>
>  If however it is a certain number of Lines that you can have against an
> account, and the line may or may not currently be assigned a phone # and a
> device, then it is a separate entity.   i.e. I have account with Verizon,
> pay for 200 lines.   190 of them are currently in use (have an assigned
> phone number).   Out of those 190, 185 currently have a device assigned to
> them (phone + SIM card).
>
> So at the moment, you have:
>
>  Accounts
>  Lines
>  Phone Numbers
>
>  Phones
>  SIM Cards
>
>  With a combination of a phone and a SIM card being a device.
>
> Jim.
>
> -----Original Message-----
> From: AccessD On Behalf Of David McAfee
> Sent: Thursday, September 24, 2020 9:30 PM
> To: Microsoft Access Database Discussion List <
> ACCESS-L at peach.ease.lsoft.com>; Access Developers discussion and problem
> solving <accessd at databaseadvisors.com>
> Subject: [AccessD] Fwd: FW: table design
>
> I need some help with a table design ideas for a new project that I’m
> starting, and was wondering if I could bounce my idea off of several of
> you. :)
>
>
>
> Currently, the company that I work for is tracking our cell phones and who
> they are assigned to by different departments*, each* in their own way and
> reconciling the lists is a major pain.
>
>
> Currently Accounting will complain because the location for the employee in
> the IT departments list is incorrect, or the employee ID in the user fields
> of the Verizon portal has the wrong Employee ID or Cost center.
>
>
>
> I want to make an app to track it in one place, using source data from each
> location (that is managed by each of these locations), so the I.T. techs
> pick an employee and device and assign one to the other and not have to
> worry about the other data.
>
>
>
> HR has Employee data (ID, name, start /end dates…)
>
> Accounting has their cost center data by employee
>
> Verizon cell data should be the source for the phone data (phone Number,
> IMEI (Device ID), ICCID (Sim number), model, manufacture..)
>
> Old / existing system to grab the current assignment of employee to cell.
>
>
> Doing it this way, if something is incorrect in regards to the employee, HR
> fixes it in their source. Accounting fixes any errors related to the cost
> center / employee relation in their system.
>
> The basic idea is simple, store the PK for the employee and the matched
> cell ID, and when reporting use the links to the other systems for their
> info (name, cost center, office location…)
>
>
>
> Looking at the flat files that are downloaded from Verizon (VZw), we have
> roughly 1500 cell phones spread across six VZw account numbers.
>
>
>
> I’m thinking of setting up something like this:
>
>
>
> tblVzAccounts (pulled from VZw download flat file and normalized during ETL
> process)
>
> vzAccPK (AutoNum PK)
>
> AccNo (Txt) 955321232-00023
>
> EDT (Entry Date Time, default value=Now())
>
>
>
> tblPhoneNumbers  (pulled from VZw download flat file and normalized during
> ETL process)
>
> PNID (AutoNUm PK)
>
> PhoneNumber (text/CHAR(10) no dashes/characters stored, only numbers)
> 2135551212
>
> ContractStartDate (Date)
>
> ContractEndDate (Date)
>
> EDT
>
>
>
> tblIineStatus (pulled from VZw download flat file and normalized during ETL
> process)
>
> LineStatusID (An PK)
>
> LineStatus (CHAR(1)- A, S, D)
>
> LineStatusDescription Active, Suspended, Deactivated)
>
> EDT
>
>
>
> tblPhones (pulled from VZw download flat file and normalized during ETL
> process)
>
> PhID (AN PK)
>
> IMEI (Text)
>
> Mfg
>
> Model
>
> EDT
>
>
>
> tblSIMCard (pulled from VZw download flat file and normalized during ETL
> process, but sims could also be swapped in the field)
>
> SimID (AN PKID)
>
> ICCID (Text)
>
> EDT
>
>
>
> tblDevice (Junction table to put all of the normalized PKs together,
> keeping a history)
>
> DeviceID (AN PK)
>
> PNID (FK)
>
> PHID (FK)
>
> SimID (FK)
>
> LineStatusID (FK) –Not sure whether to place this here, or have a separate
> junction table for DeviceID and LineStatusID
>
> EDT
>
>
>
> tblEmpDeviceJunct (Employee Device Junction table, used to mate the device
> ID with the employee ID)
>
> EDJPKID (AN PKID)
>
> DeviceID (FK)
>
> EmpID (FK)
>
> EDT
>
> EUID (Entry User ID, Auto Inserted)
>
>
>
> I’m thinking I still need a device status and/or location table, and
> possibly its PK as an FK in the junction table above.
>
> I’m also thinking of these scenarios that I’ve witnessed (even though, I
> was told they don’t happen)
>
> A phone can be damaged and in need of repair
>
> A phone can be damaged and cant be repaired, thus will need to be
> deactivated
>
> A phone can be unassigned (thinking of adding an “unassigned” employee to
> the employee table)
>
> A Sim Card can be swapped between phones (Good and non working to retain
> phone number for employee)
>
> A sim card can be removed from a phone (damaged phone laying around)
>
> A sim card can be added to a phone (New/used phone, removed card from non
> functional phone, or Extra sim card laying around)
>
> A phone (or phones) can be taken by a technician to their house to be
> worked on at home
>
>
>
>
> The following three situations can also occur, but they have to be done on
> the Verizon website, so I figure, the ETL process can handle this part)
>
> Swap phones between lines
>
> Remove phone from line
>
> Add phone to existing line
>
>
>
> Does this layout sound right? What would you do differently?
>
> The front end will be unbound and all changes are new insertions to the
> junction table, keeping an audit trail.
>
>
>
> Thanks,
>
>
>
> David McAfee
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>