Database design. I was going to put off the formal design until after we selected an engine, but it makes sense to have a high level design to help define our engine requirements. Again this was in my head I don't know why you didn't see it there
For discussion purposes, a note is the main item in the database. A user creates a
note to record information. The note could be a web page, image, text, or a combination of these things. With that in mind the data we need to manage should include:
date/time stamp for each note
note identifier (internal unique)
o note title/name
o category relationship (M-M)
o text
o blob
o template
o user **
o = optional
** opens a sticky issue. Assuming the database is avilable for anyone who can write an application to talk to it, there is no security. Should we care that User X can in theory read data from User Y database? This assumes that the files are on a shared PC and the database files are visible. Need some discussion.
Normalization: How far do we want to normalize? Should text be part of the record definition for a note or can it be in a serate linked table? Same question for any optional field except Category, which should be in a table due to the Many to Many relationship.
Some discussion is probably needed regarding how to maintain formating linkages between images and text. Also how do you want to store a web page? We should probably define those standards instead of letting the application interface determine them so you don't have data that is not transportable between two interfaces.
In the end, this becomes more of a standard than an actual database definition.