Fresh Start :) Best way to handle Note fields

[GBalcom]

Ok, at the early stages of designing a new database for a client. The application will have a few areas (let's say 5 as an example) that will have note fields. There may be notes for a Company, for a Contact, for a project, for an Invoice, for a Purchase Order, etc.

I've been taught that it's best to keep memo fields as a detached table, with their own ID, and a foreign Key for whatever they need to connect to. This keeps the Main table lean and mean.

So...I'm playing around with the idea of having something like:

tblNotes
    ID
    TypeID (Here I can separate out which type of Note it is) integer
    CompanyID (Only filled if the note is tied to a company)
    ContactID (same as above)
    PurchaseOrderID (same as above)
    etc...
    Note (memo)

So, essentially using 1 table to store all the memo field notes for the application, in lieu of 5 or more separate tables. I like this approach because it seems it can grow with the application. What do you guys think? Any pitfalls to this approach?

 

[June7]

So you will end up with lots of empty ID foreign key fields. Not really strict normalization but it would work. I once considered this in a small app but rejected and went with separate tables.

I only have separate notes table if multiple comments must be permitted for a master record, each comment having EnterDate, EnterBy, etc attributes.

It doesn't really grow any easier than separate tables. If you add a new entity that needs comments, have to add a new column.

 

[The_Doc_Man]

To correctly advise you, I think we need to know how you intended to link this "note table" to other tables. I recall that we had a similar question on the forum a while back and the discussion revealed some pitfalls. So without knowing a bit more of what you were planning to do, we cannot give you a warning about those pitfalls that might appear.

 

[arnelgp]

post #2 is already correct.

 

[GBalcom]

Doc Man,
I'm not sure what you're asking. Please ask me a question if needed to clarify. The path I'm suggesting would be to have a column for each foreign key to link each table. So, tblA, tblB, tblC, etc. would each have their own foreign key column. As already pointed out, this would lead to a lot of null values in the foreign key fields. Would this be detrimental?

 

[The_Doc_Man]

If I read that comment correctly, you will have relationships between various tables and a specific column of this notes table. You will have nulls in columns when the comment doesn't apply to the tables represented by those columns. And there is where you might have a big issue. I cannot recall the discussion in depth, but those nulls might not be happy. Or the relationships SEEING those nulls might not be happy. Among other things, relational integrity will be impossible with the presence of nulls on either side of the issue. All I can suggest is that if you try to do this, expect to find some roadblocks with regard to establishing and maintaining the links.

IF the note has to link back to the specific table and there can be more than one note to a single record in a single topic table, that will prove tricky if I recall the previous thread on this subject. I admit it has been a while but I recall some in-depth discussion that this can be somewhat restrictive. And for all I know, it might be that it was the specific method used in the original question.

 

[Gasman]

I'd probably go with
ID
TypeID
LinkID (ID to use for other tables FK dependent on TypeID)
Note
etc
and possibly a function to get the correct link description, be it Company,Contact, PurchaseOrder etc

if you had your heart set on only one table.

 

[Uncle Gizmo]

I'm wondering if you ought to go for a separate pop-up form something similar to my Nifty Help System.

Nifty Help System – Nifty Access

www.niftyaccess.com

I've just had a look through the Nifty Help System code and I think it would be possible to capture the current record number, for any form. In other words I can see that a generic solution is possible. However it would involve a bit of work to set up and I haven't got the time at the moment.

The alternative would be to change the opening function to pass through the ID of the currently selected record in the form.

This idea of a generic Note System is growing on me, I can see that opening the note pop-up form from a "Customer Details Form" and then again you could open it from a "Customer Contact Details Form", another words your note would not only be associated with the customer ID, but would also be associated with the particular form the note was made from.

 

[Uncle Gizmo]

I think the problem that Richard has foreseen is that if you just use one table, then you will have an ID field that links back to the other tables. The problem is this ID might provide the same number from two different places. Imagine you have record number 10 for customer in one table and record number 10 for contact in another table. You'll have to have another field to identify the table that the ID relates to. You would also need them indexed on the two Fields together to make sure that you never got any duplicates.

Having separate fields to store the ID's from the various table separately just seems wrong to me.

 

[Uncle Gizmo]

The nifty help system download is available for free. Contact me for details...

 

[GBalcom]

All,
Thank you for your responses. I'm about to carefully consider each one in the meantime, to ensure we're all on the same page, here is a visual representation attached.

I admit, I'm starting to think it's not a great idea, but I'm having trouble coming up with a solution.

 

[GBalcom]

I like Gasman's approach. Does anyone see anything wrong with it?

I'm thinking I could use something like:

tblNotes
    ID
    TableNameID
    TableRecID
    Other needed Fields

Then I could pull the Table ID out of here:

Using something like Select Id where Type = '1';

What do you think?

I'm not really a fan of having 10 tables when 2 seems like it would work fine.

I would also setup the index to ensure that the records remained unique. Thoughts appreciated!

 

[The_Doc_Man]

I am also a minimalist, but only when I can make it work. But your diagram revealed a HUGE no-no.

Please examine the relationships you posted. You will see a link from tblCompany:ID to tblNotes:CompanyID. BUT you will ALSO see a link from tblCompany:ID to tblJob:CompanyID and from tblJob:ID to tblNotes:JobID. The general rule is that you must have only one path from table A to table C but you have two paths - one direct from A to C and one to A through B to C. You have more than one case of this including links through tblLocates. Worse, your tblAttachments exhibits similar multi-pathing.

You ABSOLUTELY can do this - but the query wizards will probably make all SELECT queries involving the A-to-C cases as NON UPDATEABLE - because of the ambiguity of the paths.

 

[June7]

The_Doc_Man is alluding to circular reference in relationships, which will definitely be problematic for query wizards. Every query built by wizard involving the circular relationships tables will have to be modified to use preferred path because wizard will try to employ all defined by Relationships. Review https://www.codeproject.com/articles/38655/prevent-circular-references-in-database-design

 

[Pat Hartman]

Having multiple FKs pointing to different tables means that you need to change the table if you need to add a new note type so I would not consider it simpler than using separate note tables for each type. Adding a new note type if you are using separate tables, means adding a new table but unlike adding a column to an existing table does not cause any changes to existing objects.

Gasman's suggestion is fine but it doesn't allow RI to be enforced so I would probably just go with separate tables. I would keep them the same and use a single form to manage the notes to avoid excess clutter.

A way to use a single table and yet still allow RI is to create an entity table. The entity table has an ID and a type. Whenever you need to add a new row to an "entity", you add the row to the entity table and then use the EntityID as the FK and place that in your Company table or Contact Table, whatever. When you create a note, you use the EntityID to link to it rather than the CompanyID or ContactID. This technique is also good for Address tables when you have several different entities that need addresses or Contacts also if you have several different entities that need contacts.

PS,
I personally hate naming all your autonumbers ID. I give ids meaningful names. It makes them easier to match without having to look at a schema. It also works better with my documentation tool.

 

[GBalcom]

Thank you! This is why I come and post my work from time to time. To learn something from the experts. I'll need to read that article a few times before it sinks in. But I'm working on it.

 

[GBalcom]

Having multiple FKs pointing to different tables means that you need to change the table if you need to add a new note type so I would not consider it simpler than using separate note tables for each type. Adding a new note type if you are using separate tables, means adding a new table but unlike adding a column to an existing table does not cause any changes to existing objects.

Gasman's suggestion is fine but it doesn't allow RI to be enforced so I would probably just go with separate tables. I would keep them the same and use a single form to manage the notes to avoid excess clutter.

A way to use a single table and yet still allow RI is to create an entity table. The entity table has an ID and a type. Whenever you need to add a new row to an "entity", you add the row to the entity table and then use the EntityID as the FK and place that in your Company table or Contact Table, whatever. When you create a note, you use the EntityID to link to it rather than the CompanyID or ContactID. This technique is also good for Address tables when you have several different entities that need addresses or Contacts also if you have several different entities that need contacts.

PS,
I personally hate naming all your autonumbers ID. I give ids meaningful names. It makes them easier to match without having to look at a schema. It also works better with my documentation tool.

Thanks Pat. In my case, I'm expecting more than one note per record with the "parent" tables. Is it safe to say that the Entity table you proposed will not work with a 1 to many relationship? as the FK is being held in the record of the parent table? Or am I not understanding its use case.

 

[Pat Hartman]

The entity table works regardless of what the relationship with notes is. It is essentially a 1-sided junction table. Both the notes table and the other tables point to it and it is the 1-side of both relationships.

Think of it this way, it is a technique to assign an autonumber that is unique across x tables. So, in the notes table, instead of using x fields for FKs, all but one of which will be null or one field for the FK that points to x tables so you can't enforce RI, the one FK points to the entity table where you can enforce RI. To get from notes to Client, you have to go through Entity. To get from notes to to customer, you have to go through entity.

Since EntityID is stored in each table as the FK (this seems pathological and it requires a two-step update), you can go directly to notes. You don't have to go via entity. It is not normal for tbl1 to have the FK for tbl2 and for tbl2 to have the FK for tbl1 but I do it in this case so I can enforce RI. I add the row to Entity first with a null value. Then add EntityID to the record I am saving. The final step is to complete the loop and update the entity table with the FK back to the record I just saved. If you don't make the pathological connection, you cannot enforce RI. So if you don't care about enforcing RI, then don't store the EntittyID in each table (except notes obviously). An alternative to enforcing RI, is to make it a two part key. But that means that each table needs an extra column with a value that identifies the table. So tblCustomer would have a field named TableName with a value of "tblCustomer". The entity table would have three fields. EntityID (autonumber), TableName, and TableID. There would be a unique index on TableName and TableID. That way customer could join to Entity on TblName and CustomerID and enforce RI. If you do it this way then you have to always go through Entity regardless of whether you are going from customer to note or from note to customer. This is technically the more "normal" method.

 

[mike60smart]

All,
Thank you for your responses. I'm about to carefully consider each one in the meantime, to ensure we're all on the same page, here is a visual representation attached.

View attachment 86038

I admit, I'm starting to think it's not a great idea, but I'm having trouble coming up with a solution.

Hi

I would look again at all of your relationships between the Primary Keys and related Foreign Keys.

Why are these not set as Option 1 - Only include rows where the joined fields from both tables are equal

 

[GBalcom]

All,
Thank you for your advice so far.

Pat,
I'm still struggling with the concept of the entity table. I've created a new, simple test db and attached it. It includes two "parent" tables, tblCompany, and tblContact. Then a Junction table, and the child tblNotes table. It has a form and a subform for the Company table.

I can get it to add a record to the notes table. But I cannot get it to enter the tableID for the Company table automatically. Will this need to be done after the row is inserted with code? If not, what am I doing wrong? Thanks for any insight.

ps. I do plan to rename the ID's per your suggestion, just haven't done it yet.