[AccessD] Fwd: FW: table design

[James Button]

AFAIK 
Some other things to consider:

You can have multiple phones on an account
Have the SIM's moved between phones
Have phones passed on between assignees 
either intended to be permanently, for a short period, or just a day loaner.

Also - Phones assigned to 'posts', and to teams - as in manager says who gets
the phone for weekend on-call

And there are the 'personal' numbers that get directed to whatever phone the
number owner wants to use
  
Also what about tablets etc, that come with some phone contracts - so that would
be broadband usage 
Or more important - tracking that the tablet stays with the current phone
holder
And .. earbuds, handsfree, car-phone holders OK - depends on the devices being
associated with the phone - the person, or managed separately

And then there is the carry-over of unused 'allowances' months data 

Not saying you need to include those in the tables, forms, reports and data
exports
Just the design should include consideration of the possibilities and that you
were misinformed by those currently saying what is needed.

And - then consider the Microsoft 365  licencing terms:
A single non-corporate 'subscription' agreement allowing 5 users,
each user allowed a (not concurrently used?)  copy on a PC (windows or applemac)
on a tablet/mobile device and on a phone + backups?
with a legal requirement to deal with all those installations/instances when the
subscription is not continued.

Not forgetting the 'Forget me' requirements  GDPR etc. and requirements to keep
data in the 'region' where it was collected as well as fulfil legal disclosure,
and concealment demands.

Anything personal including usage - and numbers called etc.

Have fun fulfilling all those requirements simultaneously  and the combination
of shared and passed on facilities & devices.

JimB


-----Original Message-----
From: AccessD <accessd-bounces at databaseadvisors.com> On Behalf Of Stuart
McLachlan
Sent: Friday, September 25, 2020 4:00 AM
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] Fwd: FW: table design

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



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com