Starting database design - thoughts please (1 Viewer)

Donury236

New member
Local time
Today, 20:43
Joined
Jan 10, 2021
Messages
14
Can I run this past, anyone, with the knowledge? I am still trying to figure this out as IT has said NAY to a database as new CRM is coming soon (18months is soon apparently!!).

1 client can have many different pieces of Equipment.
1 category can have many brands
1 brand can have many types
1 equipment can have 1 ID (Asset number and Ident)
1 client would have 1 install date per piece of equipment at a time - but it could be removed and installed again at a later date.
1 client would have 1 removal date per piece of equipment at a time - but it could be installed again later.
1 Equipment will have many install dates with Many clients
1 Equipment will have many install Removal dates with Many clients.
1 Equipment will have many service dates - these do not depend on clients. Equipment is serviced whether on location or store.


When looking up a piece of equipment it will show all its info - where it is (client) and when it was serviced, last service Date, the cost, and if it has an agreement.

I would be pulling data from it for another company - showing the client and equipment info together on one table (that would be a query yes?)

Am I on the right track?

Relationship Diagram (1).jpeg
 

plog

Banishment Pending
Local time
Today, 15:43
Joined
May 11, 2011
Messages
11,611
I would omit Removal and Install tables. Instead, put that data in Servicing. This means Servicing gets CLIENTID and Equipment should lose Install, Servicing and Removal fields.

What is EquipmentAsset a seperate table from Equipment? A piece of equipment will only have one AssetNumber and Ident, correct?

Tables with only 1 real field (autonumbers aren't real data) shouldn't exist. That means EquipmentCat, EquipmentBrand and EquipmentType should not exist. Instead of storing their ID's in Equipment, just store the text value.

With that said, could some of those 3 fields be related? A Category could be computers, a type could be laptops and a Brand could be HP. Every company I've been at doesn't just let people get whatever they want from Amazon when they want a laptop. There's an approved list with like 3 laptops. So in that instance the data from all 3 of those tables would go into one, and then the ID from that table would go into Equipment and from that you would know the Category, type and brand.
 

Donury236

New member
Local time
Today, 20:43
Joined
Jan 10, 2021
Messages
14
I would omit Removal and Install tables. Instead, put that data in Servicing. This means Servicing gets CLIENTID and Equipment should lose Install, Servicing and Removal fields.

What is EquipmentAsset a seperate table from Equipment? A piece of equipment will only have one AssetNumber and Ident, correct?

Tables with only 1 real field (autonumbers aren't real data) shouldn't exist. That means EquipmentCat, EquipmentBrand and EquipmentType should not exist. Instead of storing their ID's in Equipment, just store the text value.

With that said, could some of those 3 fields be related? A Category could be computers, a type could be laptops and a Brand could be HP. Every company I've been at doesn't just let people get whatever they want from Amazon when they want a laptop. There's an approved list with like 3 laptops. So in that instance the data from all 3 of those tables would go into one, and then the ID from that table would go into Equipment and from that you would know the Category, type and brand.

With the category, we have things like mobile hoists, ceiling track, stairlifts, closomats, Biobidets.
So for mobile hoists, we could have a type like an Oxford 180, and they are only supplied by Brand Invacare under the category of mobile hoists. But if that can all go in one table that would be so much clearer.

I realise now that I have also forgotten to put in the Product code - that our main system assigns each brand.

If equipment loses instal, servicing and removal would I still have all those dates attached to the equipment? As the servicing date isn't client specific. It doesn't always happen when its at a clients. it could be sat in our store and be serviced - though I would like assign the first Client record to be the store so that equipment can all be logged against it when its not assigned to an external client.

I still have a lot of figuring out to do!
 

FrankRuperto

Member
Local time
Today, 16:43
Joined
Mar 6, 2021
Messages
182
I would omit Removal and Install tables. Instead, put that data in Servicing. This means Servicing gets CLIENTID and Equipment should lose Install, Servicing and Removal fields.
If a specific piece of equipment can have more than one install and removal date, then those need to be child tables of equipment parent.
As for your equipment subtyping, you need to setup Cascading Combo Boxes in your Equipmentr form.
 

plog

Banishment Pending
Local time
Today, 15:43
Joined
May 11, 2011
Messages
11,611
So for mobile hoists, we could have a type like an Oxford 180, and they are only supplied by Brand Invacare under the category of mobile hoists.

My method of 1 table for Category/Type/Brand would help here. You would have 1 record in that new table and then use its foreign key in Equipment. That way no one can input an impossible Category/Type/Brand combination (Computers/Office Chair/Tesla).

If equipment loses instal, servicing and removal would I still have all those dates attached to the equipment?

Yes those dates would go into Service Date. I would add a ServiceType field to Servicing which would hold the values Install, Service or Removal.

The new CLIENTID field going into Servicing could be null. When the ServiceType field equals "Service" you could just leave it blank. Or you could populate it for which client it is currently at, or even create a dummy client based on your location which designates the service was done while not at a client. If you have multiple locations that would be a good way to track exactly where that equipment is.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:43
Joined
Feb 19, 2002
Messages
42,976
EquipmentType is a child of EquipmentBrand and EquipmentBrand is a child of EquipmentCat. Therefore, you would save ONLY EquipmentType, not all three. The second and third can be derived from Type. However, if there are any m-m relationships in this tree, the situation changes.

If Equipment is a description of generic assets and the EquipmentAsset is a specific piece of equipment, then the Servicing is a child of the Asset. Since a service for a drill press isn't a service for all 5 of your drill presses. It is a service for the one with the asset tag 1234.

That means Equipment would be reduced to
EQUID
ETID
Nomenclature
and any other common fields.

The one really wrong thing though is the pathalogical connections between install/Removal/Servicing and Equipment. The relationsip between tables in defined by putting the PK of the "parent" as a FK in the "child". If you were to store ISID in the Equipment table, that would allow for only a single service and there should be many. So, the EQUID belongs in the Servicing table. And I agree with the assessment that install and remove are types of service. However I don't object to them being in a separate table but it should be a single table not two. That way you can only uninstall something that was installed.

BTW, it is easier all around if you use the PK name as the FK so don't call ETID, Type in the Equipment table. I would also use better names for the PKs so they make sense. EqpCatID, EqpBrandID, etc. That way you won't strain the brain of people who come after you. Abbriviations are fine but be religeously consistant if you use them and if there could be any ambiguity, it is better to go for the longer name.

I had a few minutes so I made the modifications I see.
EquipmentSample.JPG
 

Attachments

  • Equipment.accdb
    592 KB · Views: 442

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 20:43
Joined
Sep 12, 2006
Messages
15,614
@Donury236

What sort of business is this. Is it an equipment hire business where your equipment goes out on contract
Or are you a contractor, where your own equipment is allocated to different projects from time to time.

If you compare @Pat Hartman schema with your own, you can see it's a lot easier to follow the simple defined relationships. Even if you decide to modify them, it's easier to see how to achieve this.

With the equipment table, the install, removal and servicing dates information should be in different tables. Servicing in one, and movements in another. Equipment just manages the physical item, What is it, basically.

I think the the movements table should be in between the equipment table and the clients table, so that the same piece of equipment can be allocated to different projects, but this comes back to analysing the process, which will depend on the nature of your business,
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:43
Joined
Feb 19, 2002
Messages
42,976
If you use a separate table for Install/Remove, it should be a single row to make things simple. You install and that adds the row with an empty remove date. When you remove, you update the remove date to close the loop.

That table would be a third branch to the right off of tblEquipmentAsset.
 

Isaac

Lifelong Learner
Local time
Today, 13:43
Joined
Mar 14, 2017
Messages
8,738
My approach to PK and FK naming differs just a bit, I follow two rules as often as I can

- the primary key is always, and only, named ID
- the foreign key is named something like ID_ParentTable

However, I am open minded, and can also see how naming the primary key something more descriptive, like ServiceID - after which point, you can keep the FK named the exact same thing and pick up a benefit there, is also a nice way of doing it.
 

FrankRuperto

Member
Local time
Today, 16:43
Joined
Mar 6, 2021
Messages
182
My approach to PK and FK naming differs just a bit, I follow two rules as often as I can

- the primary key is always, and only, named ID
- the foreign key is named something like ID_ParentTable

However, I am open minded, and can also see how naming the primary key something more descriptive, like ServiceID - after which point, you can keep the FK named the exact same thing and pick up a benefit there, is also a nice way of doing it.
There are exceptions where the PK field is not the same as the table name, and not all PK's are autonumber fields. e.g.
Code:
tblDrawerSummary.DrawerDatePKID joining tblDrawerDetail.DrawerDateFKID
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:43
Joined
Feb 19, 2002
Messages
42,976
If you use consistent naming standards, The "ID" suffix would be used only for autonumbers or other long or short integer values. Other types of PKs would use CD or Num as the suffix.
 

Isaac

Lifelong Learner
Local time
Today, 13:43
Joined
Mar 14, 2017
Messages
8,738
If you use consistent naming standards, The "ID" suffix would be used only for autonumbers or other long or short integer values. Other types of PKs would use CD or Num as the suffix.
I suppose I wouldn't disagree with that particularly.

I only use surrogate keys anyway.
 

DanielSanders

New member
Local time
Today, 21:43
Joined
Feb 23, 2020
Messages
8
My approach to PK and FK naming differs just a bit, I follow two rules as often as I can

- the primary key is always, and only, named ID
- the foreign key is named something like ID_ParentTable

However, I am open minded, and can also see how naming the primary key something more descriptive, like ServiceID - after which point, you can keep the FK named the exact same thing and pick up a benefit there, is also a nice way of doing it.
I read somewhere, that it’s better to use ID as PK, because of a possible migration to sql server later on in the process.
 

Isaac

Lifelong Learner
Local time
Today, 13:43
Joined
Mar 14, 2017
Messages
8,738
I read somewhere, that it’s better to use ID as PK, because of a possible migration to sql server later on in the process.
While I appreciate the agreeing sentiment, I cannot honestly claim that I have any better or more sophisticated reason for using ID as the name of primary keys, other than, my eyes like the way it looks later one when I'm writing SQL joins - it feels visually easy for me to understand.

But others have different eyes, which find it easier to interpret other methods more easily.

I cannot immediately think of any reason why SQL Server environment prefers the nomenclature "ID"..
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 20:43
Joined
Sep 12, 2006
Messages
15,614
I often give every field of a table a prefix including the ID, so a sales order table might have every field prefixed with SO. It just makes it more apparent which table holds the field.

edit - in the above example, I know any field prefixed SO is in the SalesOrder table.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:43
Joined
Feb 19, 2002
Messages
42,976
I read somewhere, that it’s better to use ID as PK, because of a possible migration to sql server later on in the process.
What you use as the PK and how you name it are separate topics. The recommendation for autonumbers as the PK is twofold.
1. Technically, it makes for more efficient joins on large tables.
2. For the Access FE in particular, using an autonumber PK avoids the use of complex PKs containing multiple fields. You would use the autonumber as your PK and add a unique index on the multiple fields (using the index wizard because you can use the table interface to select multiple columns for a unique index. You can only do that to make multi-column PKs) And to use combo boxes correctly, You MUST have a single field as the unique identifier so if your table has a multi-field PK, you won't be able to use that table in a combo.

And lastly, my personal opinion ------ When I look at databases built by others as I do on a regular basis, It is impossible for me to visualize the relationships without looking at the relationship window UNLESS you use meaningful names as the PK and matching names as the FKs. Naming autonumbers with ID as a suffix is a standard that is widely used and separates autonumbers which are arbitrary from Codes or numbers which tend to have some meaning embedded in them. It isn't wrong to use ID capriciously but it does lack consistency and that causes extra brain cells to be called into play and mine are dying at a rapid rate and I can't afford to loose too many more wasting my time trying to figure out inconsistencies..
 

Users who are viewing this thread

Top Bottom