[AccessD] Fwd: FW: table design

[Jim Dettman]

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