I’ve used databases at a basic user level for a decade, but have never really thought about structure and i’m looking for some advice.
I have a list of contacts that are currently in an excel file that i want to put into a database and i’m not really sure how to structure the table relationships.
the key column would be contact names, so that one seems straight forward, however, i have other columns that might have dozens of individual items in a cell that I’d like to have as their own items. looks something like this:
| NAME | PRIMARY# | SECOND# | THIRD# | DEPT | BUILDINGS | RELATED ALARM NAME |
|---|---|---|---|---|---|---|
| Person 1 | #1 | #2 | #3 | A | 1+ | at least 1 but |
| Person 2 | #1 | #2 | #3 | B | 1+ | possibly 50+ |
| ——– | ——– | ——- | —– | —- | ——— | —————— |
Now, ultimately, i’d like to be able to relate a person to all of their associated buildings and alarms, or, a building with all of it’s associated alarms and people and be able to replace contact information as it relates to a building, department, person’s numbers, or alarms.
Example 1: Person 1 that is associated with DEPT A buildings 1-3 and alarms 1-5. They change roles and now Building 3 alarms have a different contact associated with it.
Example 2: Person 2 changes from Dept B to Dept A, but did not change buildings. the related alarms for person 2 are now going to change, but potentially not all of them.
Example 3: Person 2 hires someone that is explicitly responsible for 2 specific alarms, so those two alarms will need to be related to the new person, but, not any of the others that are relative to Person 2.
~Typically~ we are changing data based on person or a specific alarm, but it’s possible that we may find ourselves needing to change based on department or building, or, just replacing a particular phone number.
I only grasp the very, very basics of relationships, so I’m not sure how these should be keyed. There are ~6300 potential alarms, ~100 buildings and ~300 people.
I’m almost wondering if the individual alarms should be the one to many, so the alarm relates to department, building and people?
This all started at ~6300 individual text files, which i processed into excel with each pertinent name, numbers and alarm file name. I then narrowed the contact names down to a single unique name and related the file names by referencing the unique contact name to a list of file names that contain that name and then consolidated all of the individual numbers to get a list of “actual” unique numbers to each unique name.
I haven’t begun to build this database, because of my incredibly limited experience with databases besides a basic user, i’m trying to get it “right” the first time. My primary complaint with the excel, beyond the fact that it is an excel file is that it’s a multi step process to find Person 1 as it relates to multiple file name and then go to those individual files to edit them.
Now the last part of the process i can automate using an FME workspace, if i had a database to query for the appropriate building/person and or contact info/files… hence this post.