How would the table structure for this work?

[darkmastergyz]

So I have say 3 columns in my table:

ID | Title | Link to file(eg. c:/test/test.wav)


Usually I only have 1 link to a file, but sometimes some people want to add more than 1 file. How would my table structure and code work if i wanted to add a button to my form which says "add another file"

Thanks.

 

[Banana]

If anything ever can have more than one object, this shouldn't be in same table as it is now a one-many relationship and therefore you need a child table to store the file link.

 

[rainman89]

can more than one ID have the same file link?

 

[thisisntwally]

im working on a database for archival, and i planned to give the option for additional documentation. I was just going to put a checkbox to reveal a few more controls which write to corrisponding fields on my table. Does it really add value to use a child table?

 

[rainman89]

im working on a database for archival, and i planned to give the option for additional documentation. I was just going to put a checkbox to reveal a few more controls which write to corrisponding fields on my table. Does it really add value to use a child table?

for it to be a properly normalized table, yes.
This takes care of the possibilities that one entry can have many sub entries or many entries can share one sub entry.. etc!!

 

[thisisntwally]

eats normalization for breakfast

eh. i think we have different notions of value added.

 

[rainman89]

apparently so....!!

 

[The_Doc_Man]

How about this... if you have a single topic ID and multiple reference files, your "value added" is that without parent and child structuring, your database won't work worth a crap.

Enough value?

 

[darkmastergyz]

Thanks for the overwhelming response!

To clarify: so I have a couple of records:

1 | title1 | c:\file1
2 | title2 | c:\file2
3 | title3 | c:\file3 ; c:\file32
4 | title4 | c:\file4; c:\file42; c:\file43

BUT, titles will have UNIQUE files. So, files will be used ONLY once, but each title can have more than 1 file.

How can I do this? Any examples would be great!

 

[Banana]

Look at your own list. Each title has *potential* to hold more than one file.

Therefore, title is one and files is many. Files should be stored in a child tables then.

 

[darkmastergyz]

Oh. Ok. So, to clarify, it would work like this, tbl1:

1 | title1
2 | title2
3 | title3
4 | title4

tbl2:

1| c:\file1
2| c:\file2
3| c:\file3
3| c:\file32
4| c:\file4
4| c:\file42
4| c:\file43

 

[thisisntwally]

How about this... if you have a single topic ID and multiple reference files, your "value added" is that without parent and child structuring, your database won't work worth a crap.

Enough value?

its always a pleasure to see your voice.

still. why would

1|Bob|C:reqdoc1|C:reqdoc2|----------|----------|
2|Lob|C:reqdoc3|----------|C:supdoc1|----------|
3|Law|C:reqdoc4|C:reqdoc5|C:supdoc2|C:supdoc3|

resultin([database value] = "'" & [a crap] &"'")

also, masochist that i am. i would like specific examples of how wrong i am.

i would understand if this was any kind of data that is involved relationally. but since they're really just stand alone tags associated solely with an individual record, putting them in a child table seems like busywork assigned by Dr Codd. and i've never been one for busy work.

 

[Banana]

Suppose you have made this table:

ID
Title
FileName1
FileName2
FileName3

Now what if you need one more because a title has 4? What if one comes along that needs 5?

What if you needed to update file's path; how do you know which column to look in? (you could search all columns but that's slow).

OTOH, two tables is *actually* less busy work. Seriously. You just add the files, add the key of title (which can be done automatically if you use subforms) and pesto! you're done. Same amount of input, but you have unlimited storage for files linking and far more easier method to search for it as well!

 

[thisisntwally]

hmmm. i see what your saying. with what im working on the only way to search for a document, is to find the corrisponding record, and all documents must be stored in the same folder. I still don't see the need for a child for the the two required documents (there will never be more than two) but i guess i see the benifit for the supporting documents (which could range from zero to infinate, but is almost always zero).

 

[CraigDolphin]

Banana beat me to it...slow typer I am

putting them in a child table seems like busywork assigned by Dr Codd. and i've never been one for busy work.

You sound like my boss. Keeps complaining I make my data structure 'too complicated'. This, after years of him trying and failing to make an Access db to store the data he wants stored. My response is always that my databases work; and the day he can make one that does the job as well as mine is the day that I will agree with him.

If you add x fields to your title table to store up to x different files, what do you do when some turkey wants to add x+1 files? If you want busy work, you've got a whole mess of it right there. Adding a field to the table, adding to any queries that might be needed, adding control to form(s), report(s) and adding/editing vba code that may be required. Especially when your db is already deployed and in use. Two days later, the same turkey comes back with x+2 files to associate with a title...time to do it over again.

That approach also makes more work in getting summary statistics: for example, to query the number of files associated with titles requires something like a nested conditional if statement, the alternative requires only a much simpler DCount. Oh, and you can only nest so many iifs in a query (7 I think), so at some point you might find yourself hitting a more intractible problem.

Using a child table to link titles to files there is never any such problem since you set it up with the flexibility to grow as needed.

 

[Banana]

CraigDolphin makes excellent points; I may reply faster but I wasn't able to come up with strong point like he did.

Trust us on this. You really, really, really, really, really, for God's sake, blue-ever-special, friggin' DO want your database to be normalized.

I pity the poor fool who has an abnormal database.

 

[thisisntwally]

Bellingham eh? I grew up in Seattle myself. (im in ohio now, dont ask)

I see what your saying about turkeys but the reality is a. only two documents will ever be required until congress authorizes changing the budget allocation process for contract modification, which is unlikely to happen until we get our a$$es out of the middle east and b. i have been conviced to put the optional support docs in a child form for turkeys.

Tell me more about the summery problems. Im curious as to how this will get problematic...

 

[The_Doc_Man]

there will never be more than two

Oh, really? You've shown samples with THREE already. (Your sample from 10:45, title 4 - had 3 files)

Wanna put a side bet that one day your boss will come in and say, we have to allow for another file?

Wanna put a side bet that one day your boss will come in and say, we have to allow for another TYPE of file?

But here's the kicker.

Some day, your boss is going to say, What title references file XYZ.ABC ?

And to search your structure you need to search each column. But to search OUR suggested structure you only have one column to search. Build a query that shows title, file name, and link through title's ID number, and there is your search result in a single action.

THIS is why you normalize. It is not because you will statically store your data forever. (OK, I'm with the US Federal Government... maybe I really WILL store my data forever, or at least it will feel that way.) But you want to SEARCH your data sets, organize reports or lists based on names, manipulate or update titles without having to change a gazillion records.

Normalization is the best way to assure that you minimize the work overall in any of the tasks named above. Normalization is a "pay me a little now, pay me a lot later" sort of thing.

 

[Banana]

Well, I can tell you that just because you don't need more than two, doesn't guarantee that you'll never *ever* need more than two.

Furthermore, suppose you come to an exception to a rule? There's always an exception and the bigwigs tells that for *that* title, it can have more three because it's a special kind?

Don't shoehorn yourself into a design because it's simply not the way you do it today. Normalization makes crystal ball totally unnecessary.

Maybe CraigDolphin can do a better job of describing the summary problem; I know that it's going to be a pain in ass if you're going to summarize across the fields (e.g. from left to right), rather than across records (e.g. down). To do a Count() with multiple fileds, you'd need to do something like:

Count(Field1) + Count(Field2) + Count(Field3)

to get the total counts. And heavens help you should you have another field to add; as that mean the above code is now useless and you need to add another Count(FieldX) to get it to return to new totals.

OTOH, counting a child table is simple. It's just this:

Count(Field)

with the criteria of the key, and you'll always have the result, even if for some odd reason a title had 1,001 file links.

 

[thisisntwally]

OK, I'm with the US Federal Government...

Me too!

well im forcing the analysts to rename all files to reference the processes. so if they want the record for abcd.efg.pdf, well, its abcd.efg. which is how you search for a record to edit it, print it off, or just look at it.

the 10:45 you reference wasn't me. i think this thread is suffering from abnormality

edit: all of my silly questions aside, i think you guys have convinced me that the marginal benifit of childing out the documentation at least equals the marginal cost. which is good enough for me

Sorry if i got your blood pressure up Doc