[AccessD] Fwd: FW: table design

[David McAfee]

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