DB Design: Mobile Phones

[Philippe]

Hello,



The Service buys mobile phones and SIM cards from manufacturers and sells the same to the Buyers,

The manufacturer delivers the products (input invoice) to the Service and the service delivers products to the customer (outgoing invoice).

The phones have a serial and part number and a sim card serial number and phone number. This is a real problem because they are not the same type of data. One field will remain empty.

The database would look like this:

service -- manufacturers -- input invoice -- input invoice detail -- products -- outgoing invoice -- outgoing invoice detail -- customer

What do you think this can be like this?

Best regards

[ArviLaanemets]

You sell Phones.
And you sell SIM cards.
They are different products!

I.e. you sell to customer at least 2 items (some phones can use 2 SIM cards). OK. You sometimes you sell only phone (when client wants to replace an old one), or only a SIM card too.

Btw, phones have another essential characteristic - IMEI code, which is usually printed on bill too. And phones with can use 2 SIM cards have 2 different IMEI codes - one per SIM card slot.

So with both phone and SIM info in single table block you need:
tblProducts: ProductID, ManufacturerID, ProductClass, Mark, Model,..., where ProductClass has different value for mobiles and SIM cards;
tblProductDetails: ProductID, SerialNo, PartNo, IMEI1, IMEI2, PhoneNum (PartNo, IMEI1 and IMEI2 are filled for phones only, PhoneNum is filled for SIM cards only).

Or you split products into separate table pairs for phones and for SIM's. But then what happens, when someday you add some other product class into bunch?

[Philippe]

You sell Phones.
And you sell SIM cards.
They are different products!
I.e. you sell to customer at least 2 items. YES (some phones can use 2 SIM cards EACH phone uses only one card). OK. You sometimes you sell only phone - YES(when client wants to replace an old one), or only a SIM card too - YES.

a) Company A buys products from Company B (Supplier).

Yes, here are just two products: phones and SIM cards. Each Phone only uses one SIM card. SIM card status can be active, turned off and canceled. If it is canceled then another SIM card is purchased from Company A. The card status is not tracked in the database.

In the database we track the serial number, phone number, quantity, mobile operators, date purchase and price of the SIM card.

For Phones: serial number, model (type), manufacturer, quantity, date purchase, price.

Attached purchase invoice.

New Database diagram: Supplier > PurchaseOrder > PurchaseOrderItems > Products > Stock

b) Company A sells products to another Companies (company C, ....)

Attached sale invoice

New Database diagram: Products > SalesOrderItems > SalesOrder > Customer (Company C, D, ...)

Necessary reports:

- stock phones and sim cards (company A)
- which phones were bought by a company C, D,... and which SIM card is in which phone. Company A put a SIM card in the phone. For example, in samsung galaxy s8 (s/n: 123456789) is placed SIM card (s/n: 123654987654321, phone number: (1+)209231, T-Mobile US). If this SIM card is canceled then a new SIM card is purchased for that phone.

Is this database design able to provide these reports?

ArviLaanemets: Do I need to change your proposed database design when I wrote new facts?

Thanks!

[ArviLaanemets]

With Sim Number on purchase invoice and Phone Number on sales invoice do you mean phone number assigned by phone network provider to SIM card, or SIM card's ICCID? The first one can be carried over from old card to new one, the second one is set when the card is produced. I'll bet on phone number, as full ICCID is 19-20 characters long, and partial one (and practically useless for purchaser) is 13 characters long.

Can buyer have a say what the phone number will be? P.e. when I got a phone number once, I can have the same number for lifetime - when I buy a new SIM from old provider, or even from new provider, I simply can say, that I want to hold the old number. Even more, when I had a fixed line phone before, and decided to use mobile instead now, I also can carry the old phone number over.

[Philippe]

With Sim Number on purchase invoice and Phone Number on sales invoice do you mean phone number assigned by phone network provider to SIM card, or SIM card's ICCID? The first one can be carried over from old card to new one, the second one is set when the card is produced. I'll bet on phone number, as full ICCID is 19-20 characters long, and partial one (and practically useless for purchaser) is 13 characters long.

Can buyer have a say what the phone number will be? P.e. when I got a phone number once, I can have the same number for lifetime - when I buy a new SIM from old provider, or even from new provider, I simply can say, that I want to hold the old number. Even more, when I had a fixed line phone before, and decided to use mobile instead now, I also can carry the old phone number over.

Each SIM card has a serial number and its own number that can be called by the user (phone - mobile number). The number is not portable! New SIM card new serial number and phone - mobile number. This is required in my example.

When selling a phone and a SIM card, I enter the serial number of the phone and the card, and then I know which card is in the phone. When I change a new SIM card then I enter a new serial number for that phone. In database only this is followed.

Serial number of SIM card is 19 caracters long (ICCID).