[AccessD] Fwd: FW: table design

[Stuart McLachlan]

At any point of time you can have:

One phone number attached to one account.
One phone number attached to one SIM.
More than one SIM in some phones
Multiple phones per employee

I'd look at five tables and four link tables with records being time delimited :
Employee - Phone
Phone -SIM
SIM -Line
Line- Account

Each one with a EDT and a "superceded datetime" (SDT).
Indexed on the first field and EDT.  Second field needs to allow nulls

Current status data can be retreived  by joins between those five tables where SDT is null.










On 24 Sep 2020 at 18:30, David McAfee wrote:

> 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