Alternate names solution

[Denise2020]

Hi everyone, I am a complete newbie who had to create a database for work. I am wondering how to deal with this issue and keep changing my mind on the right way to deal with it. My problem is that it is a database which documents physical files in an archive. Each file has one object, but each object can have many, many names, as well as abbreviations, and all must be searchable. My question is how do I properly (thinking of the rules of normalisation) document all alternative names and abbreviations for each object. Right now, my very clumsy solution has been to have Object Name (Alternative name, alternative name, alternative name, abbreviations, etc) in the Object name field, just so that it is searchable. Is there a more elegant solution to this problem?

I haven't given much information about the database here, not really knowing how much detail you need. I am happy to provide more information I am able if you need it to help with my problem. Thanks in advance!

 

[arnelgp]

I can think of 2 tables.
the first one the main table with ID (PK, autonum), FileName (text).
then there is the second table FK_ID (long), AltName (text).

first table has unique record while the second one holds the alternative names and abrev on Same ID.

 

[CJ_London]

Would be good to see some examples - I'm interpreting you have file descriptions with multiple abbreviations e.g.

project X samples 2011-12-19.xlsx
samples project X 20120113.xlsx
proj sample Y Dec18.xlsx
proj samp Z 2012.xlsx


I had to do something like this for a pharmacy company where descriptions had variations e.g. (tablet/tab/tabs/tablets, Caplets/cap/caps, Pack/Pk, etc)

The 'acronym' table structure we had was something like this (based on the above examples)

PK.....Phrase.....StdFK..PhraseType
1.......proj...........2..........project
2.......project ....2..........project
3.......samples...5...........sample
5.......sample.....5..........sample
6.......samp........5..........sample


stdFK is the PK of the masterFK. So for new phrases you would find the master to populate the stdFK field (WHERE PK=StdFK) and if not found then the new phrase becomes the master. It takes some setting up and for initial comparisons, perhaps search on the first two or three characters. You can also use wild cards.

in sql we then had code which would wash a description through the table for each phrasetype to create a standard format description - at least on a word by word basis if not the order.

In my case since these were product descriptions we could not change them, they had to be done on the fly, but in your case perhaps you can run new filenames through the sql to and update them maintain a standard.

 

[Galaxiom]

I would add a context field to arnelgp's suggestion. For example to mark the record as an abbreviation or full name.

Another field might indicate a language if that is one of the factors.

 

[Galaxiom]

If there is a possibility of new contexts being added at run time you might consider a type of Entity-Attribute-Value model.

 

[jdraw]

I recommend you show readers some sample data showing the typical and possibly a few extremes of the name variation. Then, make a model and some sample scenarios and verify/vet the model. Don't be too quick to offer a final design or physical database before doing some analysis and vetting. It might also be helpful if you could tell us how archiving and retrieval is being done currently, and what exactly prompted the need for this proposed database.
Good luck with your project.

 

[Denise2020]

Wow, thank you so much for the dialog and suggestions. I am afraid that much of it is way over my head and I will need to do a lot of reading to be able to process it.

In my work, we document ammunition for EOD and demining. We have an official database for objects but there are thousands of files on ammunition we don't have enough information on to add to the official database. So we have files for every object, and in order to be able to find the files when we need them, I created an internal (only I use it) database as a sort of register for the files. It started simple enough (it had been an excel file prior to my creating the database). As I learned a bit about Access and realized how it could improve our organisation for the archive, I started adding and adding to it and it is at a point that I am happy with but wish I knew more because I am quite sure there are horrifying faults that would make any of you cry shame.

Anyway, just now I have three tables: one for the ammunition objects, one for a list of countries, and one with a list of ammunition types, since those are standarized. I am unsure how I would make a table for alternate names on an object. When I bring up an object, I need it to show all names, and also for all names to be searchable, so that if I look up one name. For each record, I have an object ID (obviously), object name, ammunition type, subtype, calibre, country of origin and quite a bit of other information that we need. But as I said, most objects have at least two names, and some can have six or eight and it gets quite unwieldy.


Apologies again if this is not enough information, or perhaps too much. When I get to work tomorrow, I will re-read and start researching how I would implement adding another table for alternate names. I really wish I knew more about

 

[Denise2020]

An example of a record with alternate names:

37 mm slhpgr m/62
37 SLHPGR62
37/52 skptr 96 slhpgr m/62
3,7 cm spårljus halvpansargranat m/62

Another example:

AA-9 Amos
Vympel R-33
Вымпел Р-33

Each one of these would be accompanied by a lot of other information, but these are the names I am trying to "work in" to the database, using one as a main name and the others as alternate names.

Thanks again!

 

[jdraw]

Denise,
What exactly do you record when you say "document ammunition for EOD and demining "?
Do you have a standard name or standard identifier such a NATO Stock Number (NSN) or similar?
I found this page/link when searching "37 mm slhpgr m/62".
Here is a very rough draft --I haven't considered calibre, country....Manufacturer/Supplier.....
I see you have different languages??

 

[Denise2020]

Our official database supplies soldiers and civilians who work within Explosive Ordnance Disposal with data and identification information for ammunition around the world, including photos, identifying features, weights and measures, manufacturer, etc, so that they might safely dispose of ammunition in their work.

We have unfortunately no standardization because of the fact that we document ammunition ordnance from around the world so yes, it includes every format imaginable and other languages, even other alphabets. We do however, include NATO names and other identifiers, but most don't have those (ammunition can be nearly 150 years old in some cases so it is nearly impossible to standardize in any way).

Your draft is intriguing. So basically the alternate names table would be three fields, the autonumber, the standard name, and then one alias for each record? So if say an object had five names total, it would have the original name as the standard name, and then four more records in the alternate names table? Sorry if I sound like a dummy, I am trying to wrap my head around it. Thanks again for your continued help!

 

[jdraw]

The alternate names table would be structure little differently. Depending on the usage you could consider :
-AliasID
-stdIdentifier
-alias
-language

This would be for searching any alias and retrieving the stdIdentifier in order to get details.
I haven't worked with different alphabets, but the forum has global members and I'm sure someone has "multi-alphabet" experience.
I have worked with NAICS codes (English, French and Spanish) all Latin alphabet.
In another application of ~50K companies we dealt with Legal Name, Operating Name,
Alternate/Alias Name in English and French. Companies had unique identifiers, and the Names were searched to find specific company. In fact the Names (all versions) were broken into fragments(dropping the common "in, and, the.... etc.", and each fragment was associated with the company identifier. There may be other options as details become available.