2

I've never really had a cellphone (that I owned the account to...long story), but at work we have them, and we want to get a series of relations between accounts, devices (mifi's, tablets, cellphones), sim chips, data plans, etc straightened out at work.

My personal theory on this, is that we need to put all the information into several tables in a database until the issues are resolved and the information there is valid.

And later maybe we could even add a history table if need be to remember who had them, where, and which plan / which department, and how much each plan cost. We have plans in two companies Verizon and AT&T.

So what I am asking is, what are the basic relations between this information?

From what I understand the device id's (the IMEIDs) should be the unique key that uniquely identifies a device.

I'm not certain what identifies a plan, maybe an account number...maybe a phone number...

I'm not sure what all the variables are related to this, but I'm pretty sure I could normalize a database for it, if I would find out all the unique keys involved.

I also have an HR database that keeps track of our employees that I can do a join on to record a history of plans, phones, devices etc...

Improve this question

2 Answers 2

Reset to default
3

Here are the layers of abstraction that you need to go through to track who has (pays for) a GSM phone:

ERD

Note that this is good for a point in time. If you need to track changes over time then everything becomes many-to-many with start/end dates in the intersection entities.

Improve this answer
1
  • I think a dualsim phone connects nearly always to different gsm networks, although in general case it is true.
    – peterh
    Commented Apr 16, 2014 at 7:22
2

The core of the database must be the account, also the customers. They are giving the money for the company, there isn't any other possibility. Logically everything should reference some of an account.

Second, the users authentifies themselves for the network of the company by their sim ids. Every sim card should have exactly one account (which will pay for its calls), but an account can have multiple sim cards. Thus it is a many-to-one relation.

IMEI id-s are ids of the phones. The mobile company knows them, because this numbers are communicated on every logon, but normally the customers will be identified - and their contract is about - their sim card numbers.

The IMEI Id-s aren't really existing in this database, altought they can be (and mostly, are) logged by the cellphone company.

In this this case, a login of a cellphone is the contacting entity between the sim card id and the imei id.

Thus the relation diagram is the following:

account <--- simcard <---> phone_number <--- login ---> imei_id

I extended the relation diagram with the phone number, although in this last I am not sure. In normal cases it should have a 1:1 relation with the sim cards.

Improve this answer
4
  • Where's the phone number?
    – leeand00
    Commented Apr 14, 2014 at 15:54
  • 1
    @leeand00 You didn't mean it in your question. The GSM communication happens between sim card ids and not between phone numbers. I think it is in 1:1 with the simcard, but I am not sure. I extended my answer with this.
    – peterh
    Commented Apr 14, 2014 at 15:57
  • 1
    IMEI identifies a device. SIM cards have an ID number too (ICC ID). A phone number (SSID) is assigned to one SIM (at a time). So there are lots of layers of abstraction. A person holding a phone has a phone number, but only because the phone (IMEI) has a SIM (ICC) in it and the SIM is assigned a phone number (SSID). Since the mobile operator wants to get paid, the SSID is linked to an account (some operator-specific account ID scheme). Note that an account (might) have many phone numbers. Each phone number probably has one plan (at any given time).
    – Joel Brown
    Commented Apr 15, 2014 at 4:00
  • @JoelBrown Thank you very much - if you make an alternative answer, I will upvote it.
    – peterh
    Commented Apr 15, 2014 at 10:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.