Database Design Question

[morgandawn]

 If you are designing an Excel database with creator names how do you handle entries where the creator is listed as "possibly Morgan Dawn"? 

I can think of several ways...

Creator:  Unknown-Morgan Dawn might be best.  That way if you sort, all the Unknown-Morgan Dawn works will still be listed together.  They won't be listed next to the confirmed Morgan Dawn works, but still searchable. If and when the ID is confirmed it could then be moved to "Morgan Dawn" or even updated to "Mary Sue Lamb". It could also handle multiple guesses: "Unknown-Morgan Dawn-Mary Sue Lamb

The idea is to leave "Unknown" for those were we have no clue, and keep the ones where we are guessing in another grouping.

The other way is to have a second column that would add a Y/N in  "Presumed Creator" but I don't like separating data. Plus one more cell to fill in is a pain.

Comments

[laisserais]

The best way to maintain data integrity is to separate the known/unknown into a separate column. The reason is that the concept of known/unknown is not the same as the concept of a writer's proper name, and therefore needs to be a separate field.

Especially since you want to use unknown as a choice for name, moving their status to a separate flag column will give you maximum flexibility for the future, as well as aggregability.

[laisserais]

oh and as for it being a pain: yes, but you can get around this if you have already started your project. You can insert columns, then choose 'text to columns' on the data ribbon. Choose 'delimited' and then tell it to separate on the dash or whatever you've used; it'll do a mass edit on the whole column. :)

[morgandawn]

OK, so would it work this way?

Vidder Name Unknown
Vidder Name - Best Guess - Morgan Dawn

How would we handle multiple guesses? Ex: I have narrowed this vid down to wither Morgan Dawn or Mary Sue Lamb

[laisserais]

So your field headers would be:

Vidder Name | Confirmed (y/n) | Name of Vid | etc

and the data would populate like so (each return is a new row):

Morgan Dawn | Y | your-vid-name | etc
Unknown | N | another-vid-name | etc
Mary Sue Lamb | N | another-vid-name | etc

and you're probably not gonna like this, but to handle multiple guesses, the best way to do it is to create a new row for each guess, with only the vidder name being different.

That's not intuitive, because it feels like it's redundant--and if you're only ever gonna be using this for personal use and only reading it with your eyes, then you don't have to--but if this is ever going to grow or be used as an actual database, then it's best to do it that way. I call it 'machine readable' -- it's called first normal form.

In an actual database, you'd want to do this and attach a unique identifying number to every vid name and every vidder name.

Doing it like that will also help you set up pivot tables, where you can condense info and sort quickly.

[morgandawn]

A vid entry will always have

Title Fandom (even if unknown) Vidder (even if unknown)

Each vid will have its own unique identifier. Since most of the names will be "unknown" we want to hold off assigning identifiers to them. We also need to address fan names/real names. Other constraints - this must be worked on within Google Sheets (to start).

Col 1 Vid Title XXXX Col 2: Vidder Name Confirmed Y/N (in this case N) Col 2 Vidder Name Unknown Col 3 Vidder Name Alternate 1 Morgan Dawn Col 4: Vidder name Alternate 2 Mary Sue Lamb

For a known vid

Col 1 Vid Title XXXX Col 2: Vidder Name Confirmed Y/N (in this case Y) Col 2 Vidder Name Morgan Dawn Col 3 Vidder Name Alternate 1 ---- Col 4: Vidder name Alternate 2 ----

This is not for personal use - this will be used for many other purposes and many people.

https://mailtrack.io/ Sent with Mailtrack https://mailtrack.io/install?source=signature&lang=en&referral=morgandawn@gmail.com&idSignature=22 <#>

[laisserais]

Here's an example of what I mean. If you want to build something that will be useful long term, this is the way to go. I think you're on the right track!

[morgandawn]

Thank you. The only time we would have any alternate names would be if the Vidder Name was not confirmed. So in your example, the Vid Alternate boxes would only be filled in on the second line.