Creating a database

[WayneSteenkamp]

My database skills are average but know enough to get by with the basics.

My question to you guys is:
I need to create a database that records Simcards, Phones, Repair history to the phone if any,
The complicated part for me is theres a few options to record the data.
1) A table that records the Simcard assigned to the phone,
2)if the simcard is "Cancelled" i.e damaged and needs replacing, "Assigned" to a Phone or on its own "Unassigned"
If the Simcard is damaged "cancelled, It is tracked/recorded when and why.So a SimCard History Tracker table? The phone will then be assigned with a new simcard.

How many tables do i need?
I'm thinking:


2 tables for the phone - 1 which has the details and the other tracking the history. (Could have a form with a subform with it?)
2 tables for the Simcard? 1 for the SimcardSerial numbers and the other showing if its Cancelled/Assigned/Unassigned

Am I on the right track?

[jzwp11]

You will need a table for the basic phone information. Also, since a phone can have many SIM cards over the course of its life, you have a one-to-many relationship. Additionally, a SIM card can be placed in multiple phones over time which is another one-to-many relationship. When you have 2 one-to-many relationships between the same two entities (phones & cards) you have a many-to-many relationship which is handled with a junction table.

tblPhones
-pkPhoneID primary key, autonumber
-fkManufacturerID foreign key to a table that holds all manufacturers
-other fields related to the phone


tblSIMCards
-pkSIMCardID primary key, autonumber
-txtSIMCardID
other fields related to the card

The Junction table

tblPhoneSIMCards
-pkPhoneSIMID primary key, autonumber
-fkPhoneID foreign key to tblPhones
-fkSIMCardID foreign key to tblSIMCards
-dteEndEffective (when the card was uninstalled in the phone)


You can use the above table to determine which cards are assigned versus which ones are not. I do not know if that is sufficient to address the status of a card.

Regarding the status, you could have a table to hold events related to the card such as when it was purchased and when it was trashed, but you would not need to track assignments since that is handled in the table above.

[WayneSteenkamp]

Cool, Thanks.

Next part i'm stuck on now is the 'Assigning' part.
In my SimCard Table I have all the Sim cards entered as 'Unassigned' as Default. Which is what I want. It's in a dropdown menu with the other two texts "Assigned" and "Cancelled".
What I need to be able to do now is when I'm on the PhoneID table, and attach a simcard to it, it needs to have the status change from "Unassigned" to "assigned" in the SimCard table.

Any ideas on this?

[jzwp11]

First, although Access has the capability of having lookup fields at the table level, it is generally not recommended. Having lookups at the table level can cause several issues as detailed on this website. As a matter of fact, you do not even need the field you are discussing. If you add a SIM card to a phone, you would add a record in table tblPhoneSIMCards. As long as the dteEndEffective field in that record is null, you know the SIM card is assigned. If a SIM card has either no corresponding record in tblPhoneSIMCards or if the record has an end effective date, you know the SIM card is unassigned. You would not have to update a field to designate the status, you would only need to run a query to find the current status.

[WayneSteenkamp]

I understand what your trying to say. Stating the obvious the client still wants a status displayed on the table/forms showing it. Any ideas?

[jzwp11]

You can still assign a status based on the query results, that is not an issue. You would just use an expression in the query.

[WayneSteenkamp]

Oh right, Fair enough.
Can you help start me off?

Thanks

[jzwp11]

I have provided a framework for the table structure in my previous posts to get you started. The next step is to create that structure in a database along with other fields/info applicable to your application and populate it with some test data.