beginner - easier to have big table & subdivide it or have couple table sharing ID?

[pandzio]

beginner - easier to have big table & subdivide it or have couple table sharing ID?

[beginner question - easier to have big table & subdivide it or have couple table sharing ID?]

I plant to have a big database that are on the same level of concept, but they are in different topical/thematic groups.

To simplify, let's say:
The main level data would all have the structure:

Columns:
- Source/author*
- tag level1
- tag level 2
- short keyword*
- long RTF memo (usually, but not not always)
- image (one or couple, not always, but the cell for image/file should be for every row to put there if needed in future)
- date
- [if kept in one table: - TOPIC (the sort of sub-table name equivalent)
​

They would all share this structure
But they will have different other columns.

Let's say the TOPICS are:
DIARY -
- author*: me / someone else
- tag level 1: work related / meeting / private / idea / personal-project-B / ...
- tag level 2: (more precise)
- short keyword*: meeting with M / ...
- long RTF memo: description
- image
- date
- [TOPIC: diary]

MEDICAL
- author/source: dr Martin Stouk / ..
- tag level 1: subtype of medicin specialisation: dentist / dermatologist /...
- tag level 2: type of visit (blood analysis / consultation / ...)
- short keyword*:
- long RTF memo: description
- image: scan of medical document
- date
- [TOPIC: medical]

EXPENSES
- author/source: shop/manufacturer/party that was paid
- tag level 1: shopping / bills / services / travel / ...
- tag level 2: clothes / devices / house bills / car bills / books / ...
- short keyword*: name of thing/service bought/paid/lent (trousers)
- long RTF memo: description
- image: image of thing/service
- date
- [TOPIC: expenses]

All topics would have their own columns and their own sub-relations in separate tables (like all the data about this specific dr Martin Stouk, or about this shop or about the person/friend I am strong important dates for; to mention only the columns given above)

And a third table or set of tables, which will be NOTES - paragraphs from books and websites
- author: person/website-of-unknown-author
- source: book/url
- short keyword*: name of thing/service bought/paid/lent (trousers)
- long RTF memo: description
- image: image of thing/service
- date: (often empty)
- TOPIC: history of astronomy / history of language / ...
Here also each topic would have different following columns, but they will share the above ones witch EACH OTHER and with those above in BLUE. But not all of them.

And one AUTHOR could be the same for several of the TOPICS both blue-ones and the Notes-ones. Primarily I MYSELF would be the same author for all or most of the (sub)tables of both types (same AUTHOR ID, I suppose). But I also have authors who write for different NOTES:TOPIC as well as I have bought something from them (TOPIC: EXPENSES)


So there will be let also AUTHOR TABLE
that will also store all the topics
and have shared columns for them all:
- author ID
- person? company? ...?
- last name
- first name
- picture
- [somehow to be indicated: TOPIC which have this as an author. Probably to be learned how to do that, as I don't know]



Now the QUESTION:
If I would want to be able to see them all in one table/form/report
Is it better to

A) have them in one TABLE, but work on separate TOPICS in separate Forms. Am I able to have a Form that is already filtered to consist only "TOPIC: MEDICAL" and all I input in that Form would appear in main Table with the "Medical" added into the TOPIC column automatically?

B) have them in separate main TABLES, but also have a TABLE/FORM that would display data from all of them (or another table with just three chosen TOPIC types) - aligned to the above listed in blue color shared columns, and having lots of following columns - most of which apply only for specific TOPIC type rows.
This probably might stop working if the number of columns ends up bigger than Access limit (I learned it has such limits to columns. Is that true? To the number of rows too?)

How to approach that as a beginner, to give myself more flexibility if I change my mind about the structure. How to approach that merging later vs dividing later when I would have more idea about other columns will I have and will be changing the sub-relations/sub-tables (several info pertaining for one column-x-row/cell of main Table).
The example is not perfect, but it illustrates the idea of having different set of data that share a lot on the highest level of generalities.

There will be a potential problem to think through: There will always appear something that fits two or three TOPICS at the same time.

 

[MarkK]

I recommend you do some reading on the Entity-Relationship model . . .
https://en.wikipedia.org/wiki/Entity–relationship_model

and Database Normalization . . .
https://en.wikipedia.org/wiki/Database_normalization

before proceeding. I think understanding those principles will largely answer your question, but also help you avoid other design pitfalls along your way.

Hope this helps,

 

[The_Doc_Man]

Re: beginner - easier to have big table & subdivide it or have couple table sharing I

Absolutely concur with MarkK but will add this side comment.

Usually, if you have two tables linked by a common ID number in a 1:1 relationship, you have created a very difficult situation to manipulate - because you will never be able to add or delete records to only one of the tables at a time. You will forever have to do a JOIN of them - because if you have relational integrity turned on, you will NEVER be able to add or remove records unless you do it to both tables at the same time. If one of the tables is a one-to-many case, however, then the problem changes.

So to answer your original question directly, don't split your tables according to some thematic group. Have the thematic group as a field in one table and use queries to filter out the parts you DON'T want to see if you are working on one group at a time.

 

[pandzio]

Re: beginner - easier to have big table & subdivide it or have couple table sharing I

Thanks guys!
I am looking at presentations on database theories and concept on normalisation especially. But this is just theory with examples that are simplified (perhaps I should rather read, as you suggest, than watch presentations). And I am a complicated thinker. So I'm trying to see where the limitations are, because I hate going through "boringly slowly presented" baby steps believing something is possible only to find out much much later it is not...

So generally:
If the structure of my database consists of long-RTF memos, of which all have a source-column and different categories/topics (actually several levels of sub- and sub-sub... and so on topics) as well as totally different columns for each category/topic, than it is safer to start having that in one big table, right?

Logically thinking, with my (not professional!) experience with Excel - whenever something is divided in separate tables, it becomes useless (if I want to add something new, I have to make that other table from scratch), while big spreadsheet for everything with tons of rows and tons of columns adds up with better usability later on, even if scrolling through the bazillion of columns is slightly annoying.
So perhaps it is just the same in Access.

 

[jdraw]

Re: beginner - easier to have big table & subdivide it or have couple table sharing I

pandzio,

In addition to what others have said, you may want to do some research of thesaurus/thesauri.There are many articles - a lot of theory - on storing images, formatted text etc. that aren't necessarily following a relational model. Your requirement to store RTF, which to me implies, storing presentation with the data structure, may lead you to other tools that can store html/sgml etc.

What do others in your discipline/field of study use to deal with their data and analysis?

You may want to research -museums, historical artifacts, archeological databases etc to get some options/ideas. I'm not against your using Access, Oracle etc, but I don't think their ability to store and retrieve images and rtf etc. is their strong point. Could be the best option, but do some research, and make your decision from a point of knowledge,not a guess.

Good luck.

 

[pandzio]

Re: beginner - easier to have big table & subdivide it or have couple table sharing I

Thanks jdraw!
I'm a total newbie for databases in general.
Can you help me a bit more?
Thesaurus is a common word. Searching for thesaurus +database (+create) gives mostly results that are tutorials for users of some library search engine that use that.

I only found
[sorry, I'm not able to post direct links yet]
site: quora.com inurl:What-open-source-thesaurus-building-tools-are-available
and
site:taxobank.org/content/ inurl:thesauri-and-vocabulary-control-thesaurus-software#oracle
which (the second one) is too overwhelming (though it says that Oracle probably bundles with Oracle Text that if I'm correct, does that, perhaps that's safe to have sth fully compatible with common main database-type environment, but maybe not and exporting is not a big deal between any database environments)

Hmmm... How do I "taxonomise" my search for that?
Or maybe there is an easy start point, you know of to learn it generally (especially how it handles text & images, how one can or cannot control it, i.e. select parts of it and tag vs automatic search of the file content, and what about scanned flat-image PDF-s without the selectable text; not just how you model the nodes and broader/narrower-term relations)

I am apparently surpassing my peers, this in a real "field of study" sadly, just people working individually - some with ancient methods or not at all.

 

[jdraw]

Re: beginner - easier to have big table & subdivide it or have couple table sharing I

Start with a list of what you want the database to do ideally
Pare it down or tag these items as --must - would like--future
--make some points re what will be stored
--what and how things need to be retrieved
--what and how often do you do additions? updates?
--what sort of reporting??

see this link (I found via google) re some literature on a database/document management system

This is NOT any sort of endorsement -I simply found it and wanted to pass it on.

You may find that SQL Server can do some of the things you want. I mention that because SQL Server express is free, and can use Access as a front-end.

 

[The_Doc_Man]

Re: beginner - easier to have big table & subdivide it or have couple table sharing I

There are arguments all over the place about storing long RTF memos vs. having external RTF memos as separate and uniquely named files for which you merely have a link that can be followed to open the file. Ditto, graphics-oriented or image-oriented files.

Using the general theory that the map is not (always) the territory, and remembering that queries CANNOT search memo fields anyway, you have to ask whether you would do better to have a "keywords table" associated with the table currently holding your memo fields so that you can search for keywords, and THEN if you need to dink with the contents of the RTF, open it separately. Use the "keywords" as the map to select the right territory as embodied in the RTF file. This also makes your database smaller even though you manipulate more data in total, since you don't store the Memo field inside your database. Storing big-ugly memo fields in a database eats deeply into your free space, since each database file is limited to 2Gb size. If you have big-ugly RTFs external to your database, you cease to care about how big they are and only worry about how to find them and open them when you need them.

Think about that before going forward with the design you presented earlier. I have found that on most modern computers, the time it takes to pop up a file in a separate window is small enough to make it practical, and there are items you can find on-line about finding windows, moving them, resizing them, etc. OK, it ain't the easiest thing in the world - but if you find a convenient bunch of subroutines, you have done the hardest part of the work right there. The rest ain't hard - it's just tedious.

 

[jdraw]

Re: beginner - easier to have big table & subdivide it or have couple table sharing I

pandzio,

Review this thread. It's a discussion I had with Fipper_SG.
Let me know how it goes.

Good luck.

 

[pandzio]

Re: beginner - easier to have big table & subdivide it or have couple table sharing I

OK, so all you guys made me almost give up..>
After some thought:

I want many little things, but if I concise myself to what I actually WANT for now (as a total novice) it is one thing. And relational structure is actually not required. It'd be smarter here and there, but it seems that Access cannot handle that thing I WANT.
I am looking for something better at what I am doing now in Excel.

My databases in Excel have such rows:

A) formula for VBA that would filter only rows that have in one of crucial columns the same text as I type in A1 cell (I'm not able to do such VBA myself, but it helps me greatly to filter rows and bulk-enter the value in relevant keyword-column for them).

B)-D) several levels of general topic-keywords

E) short note what is it about - for quick skimming through rows (often temporarily empty)

D) piece of data - the main element of database. Either RTF memo (one or few paragraphs/end-of-line-brakes) or placed picture/pictures (or both, but I try not to).
Several memos make up a source material. A scanned and optically recognized to RTF text format paragraphs of a book or article. I usually paste it to be one paragraph per row. But often I "explode" it to several rows if I can make of it a bullet-list structure. If I get an table or an image/graph, it goes usually to a separate row under the same column. But sometimes, if several lines of the text are better to be not divided I leave couple paragraphs in one cell.
I do so, because I am tagging each memo as a separate entity. Yes, these are shorter and longer articles and whole chapter or whole books, but I filter it to display only pictures that are portraits, only the rows with paragraphs titles and first paragraphs of a chapter, to quickly skim through what I want without distraction, or I filter it to see every source I have mentioning a specific manuscript - and I see only the relevant paragraphs/memo-chunks.

E-and-beyond)
Columns with all sort of keywords

With my limited knowledge I had to resort to Excel. But I know it is struggling with that kind of data and I am struggling too, and soon it will probably crush beyond recovery if it comes to its limits.

Ideally I would love an environment that connects the capacities of
Word-like text editing of data on the go (formatting, reddening the errors not found in spellchecker, upper/lower case, etc., available directly in the many-rows view)
+
Microsoft OneNote - type something as an idea pops or paste something for later, tag it later.
I love that it enables to drag and move up/down with a mouse any paragraph or individual rows of tables. Good for example when I want to move the row with the footnote directly below the row it is referenced in, etc. I do some rearranging, breaking down rows or merging two memos. Apart form that it is not a very good software with lots of bad ideas.
+
Evernote
Is a better note-taking app than OneNote, as it allows to assign tags to each note (they are HTML I suppose - long formatted text, bullet list, tables, images aligned to text in one note). But I cannot display these notes as a table with filterable columns, so its not good to view more than one note at the same time or do very much with them.
+
Excel
It lets me put lot of stuff inside its rows, though still will some quirks. Text formatting is very limited, and it doesn't like having too much of fancy stuff inside it. It's nice it allows to make rows manual- or auto-fit height, insert rows, etc., but with formatted cell's background colour or images pasted while any filter was on, it applies the properties to other cells or stretches the images - etc., it is clearly not intended for data that is not numbers and short unformatted text.
+
Real database.
I know kindergarten basics of only Access. Seems too me it has similar type of restrictions I want to avoid (memos, images). It also seems to me - that SQL-whatwasitsname database thing seems being mentioned as having several types of "sub-apps" shell I say? Like something to built a thesaurus, etc. Perhaps that's a way to go somewhere?

I am converting all PDFs into either images-chunks or text. They contain special characters often, so it has to work with anything like unicode.

There are lots of tables in my sources.
It would be good if I could have a table inside a "memo" cell. I see Access just converts pasted tables to spaces between cells (not supporting even tab-spaced data I suppose). Perhaps opening the table as a real separate Table upon double-clicking - you know, the table is both part of the cell in the main database but also a separate Table (changes in any get updated in both). I can do without it, totally. But if it such quasi relational-data is possible, that's be awesome.

But I am generally OK, I think, with working around, if I am able to easily paste and tag my chunks of "dirty formatted" text and images. I will make lots of columns for filters. And several Forms for inputting data that go with different columns-tagging (to not have 400 columns to scroll through each time.

It will be an ongoing work of constantly adding or changing data, so editing usability is quite important.


I hope I am making it wiser not only in my imagination

 

[The_Doc_Man]

Re: beginner - easier to have big table & subdivide it or have couple table sharing I

I hope I am making it wiser not only in my imagination

Imagination is what you want. Practicality is what you can achieve. Wisdom is knowing the difference. (With a nod to the "Serenity Prayer...")

Your problem as a beginner is what I sometimes call the "barrier of disbelief" - that is, you believe that it will take you forever to reach what you REALLY wanted, and have allowed yourself to be daunted into inactivity by the prospect.

To be brutally honest, it is possible that your goals really ARE out of your reach - at the moment. However, the trick is not in "making do" with what you can really achieve. The trick is in doing something NOW that you KNOW isn't what you want, but that isn't going to lock you into something that you can't upgrade later. If you take this approach, you take the method of Julius Caesar - divide and conquer. Break the problem into parts, implement stop-gap measures where needed, and get each piece working separately. Make the parts small enough that you are no longer daunted by them.

The things you enumerate are not terribly unreasonable but your actual problem is that the Office solution to your problem isn't a single program - it is multiple programs working together. You stated in your last post that you have recognized an important key to understanding what I'm saying... Excel is struggling to do what you want. Access by itself would struggle. Note or Word would positively GORK OUT over some of those goals. But you can make them work together to get the best parts of each application working for you, with Access VBA as the best and most reachable of the possible organizers of the other components.

Just remember that text documentation is the purview of Word. When you want to diddle with a document, you CAN do something such as open up a Word application to a selected file that can be edited in place - because you are in Word when you do that. Access is NOT a word processor program, so don't ask it to do what it was not meant to do. (And you have seen this same problem with Excel, which isn't a word processor either.)

You can bring up an image in an image window using hyperlinks, then let the internals of Windows translate the image for you. A keyword list is not so bad using child tables of keywords related to a given "main" record. Searching for keywords is not terrible if you organize your tables correctly to allow for a keyword search of a table dedicated to that purpose. Launching a PDF isn't that bad either, since these days you can find PDF launchers for at least the reader component of Adobe. (And I believe other MS utilities have PDF viewers that might work.)

Where you come into grief is trying to reach those individual goals from a single source that would be ill-adapted to most of the tasks and good at only a few of them. I doubt that anyone else on the forum will contradict me when I say that, other than some custom software that folks here might share with you (or not), there is nothing available that does all of what you want under one wrapper. The usual response to that statement is to either pare down your goals or amp up your skills and start writing.

Only you can attack your problem. We can offer advice, we can show you sample code, we can provide web links to relevant articles - but most of us have other jobs that prevent us from writing stuff for you with considering compensation of some sort. (And no, I'm not offering authoring services either. I've got other fish to fry.)

What YOU have to do now is some pretty deep soul-searching about how long you can go on like you are going now with Excel as your host platform before you scream, throw your hands in the air, and say "I can't go on like this, it's too clunky!" Then decide what you want to do - and when. As I said before, you face barriers of disbelief in your ability to manage the problem via Access. I agree that (given your statements) you probably can't manage it now. But you can learn, and that is where you have to start.

 

[pandzio]

Re: beginner - easier to have big table & subdivide it or have couple table sharing I

Thank you for your answer, The Doc Man, I almost lost all hope, that anyone would bare with me any more on this.

When I will be fed up with Excel? Like... right now?
I stopped using my database (it was a counter-depression vocation, not any more).
I think I already lost or started loosing my hope in Excel.
I am wasting too much time with the ridiculously inefficient results.

Learn to programme myself? I know I should.
[PERSONAL MOPING BEGINS HERE]
Not happening in good couple of years. I'm not as good with English as I pass like, have only small random chunks of even html. I suffer from incurable fatigue for over a decade, where I have difficulty to focus on what I read and I'm zone out more often than not. I do thing depressingly slowly comparing to others.
Actually running a database enables me to focus.
[/PERSONAL MOPING ENDS HERE]

Now regarding the concept to work in Access with hyperlinking to images and documents.
Generally I do see it as a way - if my database would be a bit different. With how it is - I'm doubtful.
I do understand linking images - as it saves the filesize of database, with not much influence of usability: the image can be still displayed.

Now with the texts... Here is more of the problem.
I am not working on the level of document (hundreds of documents and I tag them - that is the file is an item).
I don't have documents at all. I am rewriting documents and I tag their parts on the level of paragraph.
Right now I have over 60,000 rows of memos. And this is not even a significant part of what I want to cover.
I can't imagine having hundreds of thousands .DOCX, RTF and TXT files with most of them containing one sentence to ten sentences.
Also, I hope I am mistaken, but I assume that Access will not display the content of them in the table/form and I would have to click them and wait 2 or more seconds for text-editor to open them.
And Word is not a good piece of software, it adds tons of unnecessary terrible html trash to every word you type, it's a waste of disk space and memory (it's a relict of the printing era, while now the priority should be on clean and light web-friendly coding & formatting, not a fat hermetic build-up of revamped-old software)
My RTF memo-chunks are only occasionally fancy, most of the time it's just what I see Access handles (displays, enables to control in RTF memo field).

And most important:
This is not a kind of database that people normally think of.
This is actually a mega-book that I keep on reading and rewriting.
I filter what I want to read and then read and adjust. A lot of adjusting.
So my dream is to have better day-to-day performance (filtering, reading multiple rows of memos at one glance, making changes on the go), so to have what I am doing easier than in Excel, not making it more time consuming than it already is.

And from what I found, Access might be not (hopefully not) that different from Excel (better here, worse there), so I am wondering, maybe I am looking in the wrong place. I don't know anything of data management so even don't know what my options are.
It's hard to "just start anywhere" in a field completely new to you when you already know you don't want to go the default path.

P.S.
I must be on the annoying side of the question-askers. I really appreciate bearing with me.

 

[The_Doc_Man]

Re: beginner - easier to have big table & subdivide it or have couple table sharing I

And from what I found, Access might be not (hopefully not) that different from Excel (better here, worse there), so I am wondering, maybe I am looking in the wrong place.

Yes, you ARE looking in the wrong place - because again, you have let yourself look at a single solution when the point is that no single Office utility does what you wanted it to do.

Perhaps you might look up these topics in Word VBA because you can try the following:

Have a single file (or small number of files) in Word format. Look up how you can use VBA to open a file in read/write mode, jump to a bookmark, and make that window the top of your window-stack. When you want to add a new topic, see how you add a bookmark and text to a Word document. Then store the document name and bookmark in your tables. You would only need a few files, not the thousands you mention. Yes, there are limits to handling bookmarks in a single document - but they are large limits. So you could easily have several hundred marks or even a couple of thousand per file. Break your information into logical units, make a document for each, and there you go.

The one thing you can do that will absolutely doom your project is to think that a single program will do the job for you. Unless you can find commercial off-the-shelf document manager software that is economically within reach, you are going to be stuck forever with the same problem - when to stop and rework this project?

I will also submit for your consideration that a methodical approach is absolutely necessary for this to be viable. If you can't focus on goals and implement the stuff needed for those goals as a set of separable tasks, you will drown in spaghetti. You have to learn when to see the forest and when to look at individual trees.

 

[SQLWayne]

Re: beginner - easier to have big table & subdivide it or have couple table sharing I

The basics of what you want to do are not particularly complicated, but it's easy to get lost in the details. I can create a basic structure based on your original post in 5 minutes and have a data entry form shortly thereafter. There are a few things in your design that you need to give some thought to.

First, Tags. As was pointed out earlier, you need a basic understanding of normalization. A basic rule is to not have repeating groups of data. You have two tag levels plus a keyword. Will each tag level contain a single word or phrase, or can it have multiple? If multiple, then it should be in a child table. Storing a value like 'Diary, 18th Century, Nautical' in one field can be done, but it makes it hard to tally how many diaries, how many 18th century, how many nautical. Same thing with keyword. And the tags could be combined in to a single child table consisting of EntryID (to link to the parent), Tag Level (1 or 2), and Tag Value. This way, if the tag Diary is sometimes entered as level 1 or level 2, it will catch all entries.

And if you want to restrict what can be entered as a tag or keyword, then you'll either need a lookup table or you'll have to enter the list in to a combo box. I'd recommend a lookup table since it'd be much easier to add new values or remove old ones.

Next, Access supports OLE Objects, Hyperlinks, and Attachments for field data types. So linking an RTF document is doable. No guarantees as to how fast it would open, too many variables to consider until you have a working model. The OLE Object can contain graphics, which will handle your images. But like tags, if you have zero to many, it should be in a child table. This also means it takes zero space if a parent record doesn't have any images associated with it.

Finally, as also has been discussed, Access may not be the right tool. It's an excellent general purpose database tool, but it doesn't always do that great of a job under heavy load, and if you start slamming it with OLE objects, it can get big really fast. What I'd suggest is to develop a basic system that does what you want, and if you can get it doing what you want, then consider moving the data side of it up to Microsoft SQL Server Express. It will support up to a 1 gigabyte database and it's free. SQL Server is designed for heavy data loads. It is, however, a radically different paradigm. If you do upsize your data to SQL Server, you can continue to use Access as your front end interface to the SQL Server back end. And if everything is running on your personal computer, you probably won't notice any loss of speed.

Regardless of whether the data is in Access or SQL Server, it has to be backed up properly to keep your data safe. With Access, you have to close the database to reliably back it up. For SQL Server, you need to use either the T-SQL programming language to back it up, or you can use the SQL Server Management Studio (SSMS) GUI to back it up. But I'd definitely start in Access to see if you can get the model working properly.

 

[pandzio]

Re: beginner - easier to have big table & subdivide it or have couple table sharing I

Dear SQLWayne,

REGARDING NORMALISATION (My issues or unfitting with it? Perhaps just not understanding the use in complex cases, I wish)

I will be working with at least 50 tag-categories for each item.
Most of them would be tick/untick or 2-letter abbreviations of several common options. But some will have free-of-rules content (anything or empty). They are organised in columns for easy filtering as I have it now in Excel.
So I can filter with combine-filtering by author, period, style of ..., type of ..., century, country, etc. to end up with just the 2 rows out of 87.023 rows I have there. I don't have to think what my options are let's say to select Eastern countries, I just tick off what I don't want or deselect all and tick what I want.
I'm not sure how I would filter/search with how you propose to treat tags (as a messy(?) pile where no tag has its parent-place, easier to input keywords by hand I assume. But when you have to tag 500 rows for each of 50 tag-categories that are relevant to this row (in bulk for a day) - than I prefer to select rows and batch input or batch tick the right keyword.

REGARDING OLE/ATTACHMENTS

My worry is that when I have to change the content of ten memos - I would have to open ten text document and somehow not get confuse myself on what is what on the screen, also not sure how they would fit to be displayed all at once to not click between them back and forth. Yes, I know you ale are saying: "You can have several memos in one file, man!". But when I will filter the database I might easily end up with memos from 10 different documents.
Also what will happen if I want to open 5 memos that are inside the same file. Will it open 5 times each one with point of focus on the relevant paragraph or will it just open the text file and I will have to search for that fragment.

And most important. Would I be even able to see the content of the memos in Access? Or would I have to guess which 50-ish of 87.023 memos to open to see which (if any) of them is what I wanted to look up?


DATA NORMALISATION VS FLAT DESIGN

I see why Normalisation is a good thing - to not have data repeated as it prevents errors (misspells and variant-synonyms being counted as different option).
But it seems to me it fails to produce what Flat Database does:
You see all the data with one glance and you can easily filter and sort by all the columns and catch all your data affected to understand and see everything.

Dividing the data to different lookup tables makes you loose the big picture of what you are doing and what your options are. If I cannot easily add new columns because the design of related normalisation tables will break, then I am stuck with what I designed in the past which proved to be wrong later.
Though if after decomposing the Flat table into small parts, you actually can glue the small parts back to display the filterable table of whole data - than that's different story. I would assume one would. But all the presentations I saw showing how to make a flat spreadsheet into better normalised database, they only break down that flat table into meaningless chunks, claiming it is superior, but never even mention gluing it back.
Is it even considered a standard use? Or is it just "easy for you buddy Computer, not your visual brain, deal with it" sort of thing?

I am afraid I might have to be doomed with Excel or something Excel like. It would be sad though that a calculus table would work better for storing informations than real data-oriented database

 

[SQLWayne]

Re: beginner - easier to have big table & subdivide it or have couple table sharing I

There's a reason why normalization is both science and art, and it's easy to get lost in the details. To do what you want in Access is largely doable (I can't address the attached/embedded RTF docs, it's outside of my skill scope) but it would be a lot of work.

Let me give you an example. Let's call your main table Master, and right now it's only going to have one field, MasterID (autonumber). Obviously it'll have more than one field in the real world. Next we'll create the Tag table, it'll have two fields: TagID (autonumber), TagDesc (text). Now we need a third table, an Intersection table, we'll call it MasterTags and it has the following fields: MasterID (from the Master table), SequenceNumber, and TagID. The PK can be on MasterID plus TagID so that you can't enter duplicate tags for the same Master record. So when you add a tag to your master record, you have a form with two list boxes. The left one is blank (for this example) and will hold the tags to be added to the master record. The right one lists all of the tags, and there are two buttons between them to add or remove a tag to the master. The left is the intersection MasterTags table.

Since the MasterTags intersection table is a table itself, you can have a theoretical unlimited number of tags per master record. There's a moderate amount of VBA code behind those two buttons to add or remove tags to the intersection table, but it's doable. Likewise, you can have as many tags in the Tags table as you like. You're only limited by disk space.

If you have 80,000 Master records, and each has 10 Tags, your intersection table has 800,000 records but it's still relatively small: three integer fields and that's it.

So when you deconstruct/normalize an Excel record like this, everything still fits together because the Master table's MasterID is branded on every child record related to it. No data is lost under normal circumstances.

This also establishes a many to many relationship. Both the Master table and the Tag table are the parents to the MasterTags intersection table. You don't directly relate the Master and the Tag tables because logically you can't if you need the ability to have multiple Tags per Master. If you have a strict correspondence of one Tag per Master, let's say Country to Shipping Address, then they can be related directly, otherwise you must have a many-to-many intersection table.

Filtering can be a slightly tricky proposition with intersection tables. While it's easy to say "I need all Masters with Tag #5", it can be a little tricky to implement that in a form. It's easy enough to do in a report, but there you're not editing data. And the implementation difference between "I want all Masters with Tag #5" and "I want all Masters with Tags #7-9" isn't all the bad, if you do something like this regularly then possibly the easiest thing to do would be to add a checkbox field to your Tags table named ReportOn, go through that form and click on the tags that you need, then join against it through the MasterTags table and you're gold. Probably have a button on the form to clear all checkmarks so you can start with a clean slate.

I think you're grasping the essence of the purpose of normalization: consistent, non-duplicative data. It's easy enough to join the data back together through queries, though it can look strange. When it comes to building reports for it, you use Master as the main data source then you link in subreports for the Tags and it all comes together.

Again, sorry I can't give you any solid recommendations on editing the external files.

 

[Rabbie]

Re: beginner - easier to have big table & subdivide it or have couple table sharing I

By storing the data once then you obviously save a lot of storage space compared to flat files. That is why a properly normalised database is an efficient means of storing and processing data. It is always easy to write a query to give you a "flat file" type dataset for a report if it is needed.

 

[pandzio]

Re: beginner - easier to have big table & subdivide it or have couple table sharing I

Thanks for the replies
I am not implying that any data is lost when "deconstructing" from Flat to Normalised. No, I know it's not lost.
I mean that - the way I saw people presented how it works (in ALL examples) - is that visually the clarity is lost.
If I want to filter Masters who have either key#5 key#7 key#8 or key#9
I don't want the Master29 who has all of them to be listed 4 times.
These sort of list where all the combination of ID from 1 table with keyword-ID from table 2 are in 2 column list makes me cringe. It is not looking structured, as opposed to flat table with individual rows per tag and only one row per Master and only one row per Keyword it is clear. In two columns ID-ID list it looks like everything was dumped without order into one heap. It is perfect from the PC point of view, it might be ok/perfect from data manager, but it looks like programming-language for a visual thinker.
In Excel you can paste things wherever and sort out from the birds eye view how to fit pieces together. With Access I see I must think like computer. Works with simple relations of Faculty-Teacher-Student-Subject-Note, but not as much in the fields that are humanistic by nature - where even deciding what is relation of items is a fuzzy art where often you change your hierarchy of items or cannot decide how you define them yet.

The fact that Access is so much "within this <<box>>" everywhere, so you cannot make a temporary inconsistencies (mistakes) to be dealt with later if even needed.
And it seem that if I cannot dump batch data and arrange it after I dumped it into the table, then I probably have to input everything almost by hand.

I am certain that visual-user-friendly magic can be done with it, no matter how mega expert one has to be for that, but it's really hard to find it being presented that way I think. Everywhere I see the raw examples of making this 2 column tables relations that just looks oldschool-borring. Like there is no chance for creative experience, it is this system with this rules, and go by them, almost like an old school teacher having to fill out the class register like a robot...

Now. I am not criticising anyone. If anything I am frustrated at myself that I cannot just creatively grasp it and jump to magician-level of computers...
Great thanks for bearing with me.

I just hope I am not chasing impossible dreams that will never even remotely manifest driving me insane

 

[The_Doc_Man]

Re: beginner - easier to have big table & subdivide it or have couple table sharing I

The fact that Access is so much "within this <<box>>" everywhere, so you cannot make a temporary inconsistencies (mistakes) to be dealt with later if even needed.
And it seem that if I cannot dump batch data and arrange it after I dumped it into the table, then I probably have to input everything almost by hand.

We hear you, but may I respectfully point this out? You have a computer there that can be programmed to handle those issues on-the-fly when you define them, and thus make the computer do automatically for you the work that you think you need to do by hand. This tells me that your inexperience is limiting your vision.

If I want to filter Masters who have either key#5 key#7 key#8 or key#9
I don't want the Master29 who has all of them to be listed 4 times.

And this is why there is such a thing as "SELECT DISTINCT MasterKey FROM ..." syntax.

I remember many times when I thought that the only solution to get things going was to let things stay broken until I could fix them. And in my early DB career, I found all too many opportunities to just throw my hands up in the air saying, "I'll let this be wrong and fix it later." Which leads to that old adage, "If you haven't got time to do it right the first time, how will you EVER find time to fix it?"

You have expressed your impatience with your current solution. I know that feeling. But sometimes you have to accept the imperfections in your current solution until you can implement the correct solution. Part of that process is learning when to run with it and let some things stay broken, vs. when to delay implementation until you can fix it right for the big deployment. The trick is always to think hard about the problem and decide "this can wait until version 1.1" but "that has to be right in version 1.0" - and then live with that choice.

The thinking process will then be two-fold. First, what do I HAVE to get right at this time, and Second, what can I leave half-done but in a way that I can easily come back to finish this part of the job. It is a tricky little circus tight-wire act at best, and I have been there more times than I know how to say. I remember being on the wrong end of this process. I also remember the absolutely great "algorasms" when it worked right reliably.

For those who have never seen the term and don't immediately understand it, ....

algorasm - noun - AL-gor-as-um - the sudden mental euphoria resulting from seeing your complex code run correctly for the first time after a long development cycle with many difficulties.