Help understanding the basics of designing a table

[Apelsin]

Hi,


I’m slowly learning about MS access (and databases in general). But I think I have a hard time grasping how to think and use access.
Right now my goal is to create a database over all devices. So, then I try to think about what I want to keep track off. In my case it´s the following:

ID - Autogenerate (primary key)
Manufacture
Device
Model
Serial number
IMEI
Status: Working, broken
Location: Warehouse, office etc
Who has it: Staff name

This is what i imagine a part of the end result would look like. But from my understanding I should break this table up? and later on join tables together to create a "final"_tbl.

ID	Manufacture	Device	Model	Serial/product number	IMEI	Status	Location	Who has it
1	Apple	Mobile	8	123456	9876454	Working	Warehouse
2	Apple	Mobile	8+	321654	7895464	Broken
3	Samsung	Mobile	S8	112233	4455664	Working		Sam
4	Apple	Computer	Air	447711	8528522	Working		John

Device_tbl – Only information about the specific device (maybe I could include “status” here aswell?)

ID

Manufacture

Device

Model

Serial/product number

IMEI

Location_tbl – Table with all the different location it could be?

Staff_tbl – Name of employees who might be borrowing it?

I hope It was possible to understand my point. I would love the hear your opinion about this, and how I can improve.

[ranman256]

you seem to be on the right track.
The [Who has It] field would be the ID, (ie: Staff_Tbl.ID ) and not name. But name can work for small staffs too.

Tho I've seen some Inventory databases where people make the error of a separate table for every device:
tPhones,
tPCs
tfax

all these can fit into a single table : tEquipment, with a EqType: PC, phone, fax, laptop, etc.
they all have make,model,serial,etc.
tho some have specific fields, i.e: MobileCarrier, I don't see it to justify a separate table.

[CJ_London]

In the context of the data you provided I would expect to see tables for

Manufacture
Device
Status
Location
Who has it

and your devices table would store the ID of each of those, rather the the text value

DevicePK

ManufactureFK

DeviceFK

Model

Serial/product number

IMEI

StatusFK

LocationFK

Who has itFK

Note that rather than ID I use PK (primary key) to differentiate it from FK - (foreign key), PK is a unique identifier for a record, FK is using that PK value to reference that record. However it is a matter of personal preference

[Apelsin]

and your devices table would store the ID of each of those, rather the the text value

DevicePK

ManufactureFK

DeviceFK

Model

Serial/product number

IMEI

StatusFK

LocationFK

Who has itFK

Note that rather than ID I use PK (primary key) to differentiate it from FK - (foreign key), PK is a unique identifier for a record, FK is using that PK value to reference that record. However it is a matter of personal preference

Thank you both for the quick response. I think this is the part where I don´t really understand the next step, on how to combine them again.
So for example the ManufactureFK table only has one column? The names of the different manufactures. I then create a foreign key (relationship) so I can establish a connection across tables?

[CJ_London]

So for example the ManufactureFK table only has one column?

no - it has two columns

ManufacturePK - autonumber
Manufacturer - text (e.g. Apple)

you can add further fields which might be relevant such as web address


you might add further tables such as country

CountryPK autonumber
CountryName text
again you might add further fields that might be relevant - population or currency for example


then with a many to many join table you can then select those countries where apple devices are available

CountryFK
ManufactureFK
and again you might add further fields such as the year that manufacturers devices became available in that country

these may be well outside the scope of what you are trying to do, but illustrate how tables go together

[ArviLaanemets]

tblDevices: DeviceID[, RegisteringDate], DeviceClass, ManufacturerID, DeviceModel, SerialNo, IMEI[, CurrentUser];
(RegisterinDate is optional. In case you use it, it must be inserted automatically, and editing it must be restricted for database users.)
(DeviceClass replaces Device field and is linked to tblDeviceClasses. Having separate fields for DeviceClass and for ClassText allows you easily change class name wording whenever there is need for this.)
(ManufacturerID is linked to tblManufacturers.)
(CurrentUser is optional and it is an ID of user the device is attached to currently. It is calculated from tblTransactions whenever a record for device in tblTransactions is added/deleted/edited. You can do without it, but it will be helpful e.g. for some queries and reports.)

tblManufacturers: ManufacturerID, ManufacturerName;

tblDeviceClasses: DeviceClass, ClassText;

tblDeviceUsers: UserID, UserName1, UserName2, UserType;
(When User is of employee type, UserName1 contains ForeName and UserName2 contains LastName. When User is of unit type, UserName1 has values like "storage", "production department", "finance" etc., and UserName2 is empty. When user is of system type, UserName1 has values like "free", "broken", "in meintenance", "arhived", and UserName2 is empty. Devices with current user = "arhived" are out of use, and aren't displayed in Devices form unless specific filter for Devices form is set.)

tblUserTypes: UserType, TypeTect;
(Examples of possible user types, i.e. TypeText values: employee, unit, system, ...)

tblTransactions: TransactionID, TransactionDate, DeviceID, UserID.
(In this table, every change of device use/location/status is stored - i.e. you have the full history of every device here.)

[orange]

I suggest you review some of the articles here to get an appreciation of database concepts. You should have some knowledge and familiarity with database principles to understand the "why" of the comments provided before jumping into physical Ms Access.
Access will not do anything you don't tell it.
Good luck.

[Apelsin]

no - it has two columns

ManufacturePK - autonumber
Manufacturer - text (e.g. Apple)

Okey, I think I start to understand. Let´s say I have these 3 tables.

Manufacture.table

ManufacturePK	Manufacture
1	Apple
2	Samsung

Device.table

DevicePK	Device
1	Mobile
2	Computer

Final.table

FinalPK	ManufactureFK	DeviceFK	Serialnumber
1	1 (Apple)	2 (Computer)	123456
2	2 (Samsung)	1 (Mobile)	987654

So to create the Final.table, ill have to create two foreign keys (relationships).
Like this:
Manufacture.table | ManufacturePK --> Final.table | ManufactureFK
Device.table | DevicePK --> Final.table | DeviceFK

[CJ_London]

you're getting the idea! Except Final table is not a table, it's a query. Tables are for storing data, not displaying it

other tips.
- Avoid spaces and non alphanumeric characters in table and field names.
- Avoid using reserved words - see this link https://support.office.com/en-gb/art...9-f855bdd9c5a2
- Avoid using lookups and multi value fields in tables - just because you can, does not make them a good idea - see this link http://access.mvps.org/access/lookupfields.htm

[Apelsin]

I suggest you review some of the articles here to get an appreciation of database concepts. You should have some knowledge and familiarity with database principles to understand the "why" of the comments provided before jumping into physical Ms Access.
Access will not do anything you don't tell it.
Good luck.

Thank you, I appreciate it !

[Apelsin]

you're getting the idea! Except Final table is not a table, it's a query. Tables are for storing data, not displaying it

other tips.
- Avoid spaces and non alphanumeric characters in table and field names.
- Avoid using reserved words - see this link https://support.office.com/en-gb/art...9-f855bdd9c5a2
- Avoid using lookups and multi value fields in tables - just because you can, does not make them a good idea - see this link http://access.mvps.org/access/lookupfields.htm

,

Oh that makes a lot of sense. Thanks for taking the time, now I have some night reading to do!