adding Notes to several tables

[Christopher Glaeser]

I would like to add multiple notes to workorders, where a Note is defined as
....

tblNotes
NoteID: Primary Key
ForeignID: Foreign Key
CreationDate: Date
EnteredBy: Person
Note: Memo

Using this structure, I could add any number of Notes to a Workorder using a
many-to-one relation. However, I have several unique types of workorders
maintained in separate tables (e.g. tblInspectonWorkorder,
tblRepairWorkorder, tblDemolitionWorkorder). Consider these two design
approaches:

1. Create a unique Notes table for each type of Workorder table.

2. Add another field to tblNotes to specify which Workorder table the Note
is attached.

Which is the best design approach? I'm guessing the latter is preferred,
but I wan't to avoid any unforseen pitfalls.

Best,
Christopher

 

[Brett Collings [429338]]

Number 2 is the correct option - no question. It's a better
arrangement of your data and will be easier for later analysis

Brett

I would like to add multiple notes to workorders, where a Note is defined as
...

tblNotes
NoteID: Primary Key
ForeignID: Foreign Key
CreationDate: Date
EnteredBy: Person
Note: Memo

Using this structure, I could add any number of Notes to a Workorder using a
many-to-one relation. However, I have several unique types of workorders
maintained in separate tables (e.g. tblInspectonWorkorder,
tblRepairWorkorder, tblDemolitionWorkorder). Consider these two design
approaches:

1. Create a unique Notes table for each type of Workorder table.

2. Add another field to tblNotes to specify which Workorder table the Note
is attached.

Which is the best design approach? I'm guessing the latter is preferred,
but I wan't to avoid any unforseen pitfalls.

Best,
Christopher

Cheers,
Brett

 

[Jamie Collins]

I have several unique types of workorders
maintained in separate tables (e.g. tblInspectonWorkorder,
tblRepairWorkorder, tblDemolitionWorkorder). Consider these two design
approaches:

1. Create a unique Notes table for each type of Workorder table.

2. Add another field to tblNotes to specify which Workorder table the Note
is attached.

Option 2 is no option: storing meta data (e.g. table names) is a
design flaw.

I presume you have table (you've probably named it tblWorkorder <g>)
for the attributes common to all Workorders, meaning you'd need just
one table for Workorder notes, otherwise I think you may have another
design flaw.

Take a look at this thread:

http://groups.google.com/groups?selm=OEFLt#[email protected]

"The classic scenario calls for a root class with all the common
attributes and then specialized sub-classes under it..."

Jamie.

--

 

[Christopher Glaeser]

Take a look at this thread:

http://groups.google.com/groups?selm=OEFLt#[email protected]

I was not able to open this thread. Is there another link to access this
thread?

Best,
Christopher

 

[Brett Collings [429338]]

Option 2 is no option: storing meta data (e.g. table names) is a
design flaw.

Ooops ... that's what I get for firing off quickie answers after
midnight. James is right.

What I read Option 2 as was to add another field to tblNotes with a
Foreign Key identifying whether it's Inspection, Repoair or Demolition

But frankly, on looking at it, I don't like any of them very much.
You are having these problems because I think that your basic data
design is a little confusing.

Why not just have a notes field in the workorder table? Each note
must be unique and unduplicated to each record so therefore there's no
reason to split them into a separate table.

Is there an absolutely compelling reason why there has to be 3
workorder tables? If not, they could all be in one with a combobox
which selects and identifies which type of workorder each one is.
Then your notes can be attached to an individual record.

If you have to have 3 workorder tables then I do think you need to
have 3 notes tables although I can see no reason for having to do
that.
Cheers,
Brett

 

[Christopher Glaeser]

Is there an absolutely compelling reason why there has to be 3

workorder tables?

No, I could combine the workorders into a subclass as suggested in a
previous response, but it would be nice if I could add the Notes feature to
a variety of tables that are unrelated. There are many tables in my
database design (I have not implemented anything until I understand these
issues) such as tblEmployees, tblVehichles, and tblWorkorders. It would be
great if I could simply add a Notes feature to each of these and other
tables using only one tblNotes table, instead of creating a separate Notes
table for each table subform.

I suppose I could create a tblTable with a subform tblNotes, and then
Employees, Vehicles, Workorders, etc. could be subclasses of tblTable.
Comments?

Best,
Christopher

 

[Brett Collings [429338]]

Christopher, I think the solution is easy. And BTW, I applaud you for
taking the time to sort all of this out now, that's exactly how it's
meant to be done. Most just barrel into forms and reports and the
whole thing is eventually abandoned as being impossible.

Don't add a notes *table* to each category, just add a Notes *field*
to each table.

Unless of course I missed the bit where you said there will be many
notes per workorder.

If there's many notes per workorder then you would probably need
either of two options
1) A single tblNotes which has a Foreign Key which is populated via a
SubForm using a Category of Workorder (one of the 3) which is looked
up from an underlying tblWorkorder which has all Workorders and looks
up a workorder category from a tblWorkOrderCategory

2) Is three workorder ("tblWoDemolitionNote") notes tables, one for
each workorder table

I don't think there's a preference, it all comes down to your business
model. If Repair and Demolition workorders are handled by different
teams and have major structural differences in yoru operations, then I
would keep them separate. If the same infrastructure deals with all 3
workorder types then I'd combine them. Best I can do on that front
sorry.

I am always careful with the use of notes/memo fields as they can't be
searched or grouped easily and often cause grief at 255 characters in
Reports.

As for all your other tables, just keep them seperate as they then
conform to Best Practice for Normalization and Data Integrity.

Brett

No, I could combine the workorders into a subclass as suggested in a
previous response, but it would be nice if I could add the Notes feature to
a variety of tables that are unrelated. There are many tables in my
database design (I have not implemented anything until I understand these
issues) such as tblEmployees, tblVehichles, and tblWorkorders. It would be
great if I could simply add a Notes feature to each of these and other
tables using only one tblNotes table, instead of creating a separate Notes
table for each table subform.

I suppose I could create a tblTable with a subform tblNotes, and then
Employees, Vehicles, Workorders, etc. could be subclasses of tblTable.
Comments?

Best,
Christopher

Cheers,
Brett

 

[Christopher Glaeser]

Don't add a notes *table* to each category, just add a Notes *field*

to each table.

Unless of course I missed the bit where you said there will be many
notes per workorder.

Yes, I need multiple notes per workorder, where each note includes the
creation date, person who created the note, the subject, and the memo.

If there's many notes per workorder then you would probably need
either of two options
1) A single tblNotes which has a Foreign Key which is populated via a
SubForm using a Category of Workorder (one of the 3) which is looked
up from an underlying tblWorkorder which has all Workorders and looks
up a workorder category from a tblWorkOrderCategory

2) Is three workorder ("tblWoDemolitionNote") notes tables, one for
each workorder table

I don't think there's a preference, it all comes down to your business
model.

Based on the comments in this thread, I'll probably combine the workorders.
The information in the various types of workorders are quite distinct and
for that reason they should be separate tables, but the number of workorders
per day is modest (perhaps 20 per day), so the unused fields in each record
due to combining the tables should not be a serious storage problem. If
necessary, I could always split the table in the future and use one-to-one
subclasses for the different types of workorder.

I am always careful with the use of notes/memo fields as they can't be
searched or grouped easily and often cause grief at 255 characters in
Reports.

I am unaware of this 255 character limitation in reports. Can you please
expand?

Best,
Christopher

 

[Jamie Collins]

I was not able to open this thread.

Apologies. It should be:

http://groups.google.com/groups?selm=OEFLt#[email protected]

Jamie.

--

 

[Jamie Collins]

it would be nice if I could add the Notes feature to
a variety of tables

You should to ask yourself, is an Employee Note the same entity as a
Workorder Note? If yes, then they should be in the same table.
However, rather than the Notes table holding a 'reference' to other
tables, I think the other tables should reference the Notes table via
a foreign key.

Jamie.

--

 

[Jamie Collins]

I need multiple notes per workorder, where each note includes the
creation date, person who created the note, the subject, and the memo.

Sounds like you need relationship (many-to-many, junction, etc) tables
for each e.g. if your main tables are:

Notes
Workorders
Employees
Vehicles

then your relationship tables would be

WorkordersNotes
EmployeesNotes
VehiclesNotes

but perhaps with more meaningful names <g>.

Jamie.

--