Messaging Databse (deep breath)

J

jr007

Registered User.
Local time
Today, 18:28
Joined
Jun 27, 2016
Messages
21
Hi all

Being new to databases, I feel I'm jumping in the deep end here, but here we go:

I need (yes, need) to create a database that allows:


  • Messages to be submitted to from a user to another user (each user will have their employee number loaded in already as their 'UserID' in the 'Users' table (attached)
  • The recipient of a message should be able to reply to the original message and these messages be extracted easily.
  • After a pre-determined amount of time, messages from the 'Message' table should be transferred to the 'MessageArchive' table.
The attachment is based on a slightly tweaked version of a tutorial I found; I'm unsure about the use of one-to-one relationships that seem to be forced by using the same primary key twice (I couldn't use 'Referential Integrity' (though I don't know if that really matters in this case)).

The tutorial doesn't really explain the relationships, just the tables required, so what I've made is probably a million miles away, in which case direction on the format would be very much appreciated!

Thanks in advance.
 

Attachments

  • DB.jpg
    DB.jpg
    65.6 KB · Views: 201
Last edited:
Just curious ---Why Access for this? Did you consider Outlook?

Can you give us a link to the tutorial?
 
jdraw said:
Just curious ---Why Access for this? Did you consider Outlook?

Can you give us a link to the tutorial?
Click to expand...

Hi jdraw,

Thanks for the reply.

It's for a couple of reasons really:


  1. Where I work, there is no Outlook for nearly every potential user.
  2. My database knowledge is slim, and I'd really like to get my head around this kind of thing (though that isn't the motive).
The link for the tutorial is here.

Thoughts?

Thanks.
 
It seems strange to have access but not outlook?

You do realise that you would need access on each machine or at the very least to install Access Runtime.
 
Thanks for the input so far.

The solution I'm after is the solution I need. Whilst I appreciate the mention of alternatives, if they were viable, they would be used.

If I'm honest, even if alternatives were available, I'd still be asking the same question in order to further my understanding and therefore my desperation remains!

Again, thanks for the attention so far; I'd welcome ideas on a solution, given my approach (database (Access is not being used as a front-end)).
 
So, a couple of things here...

1. That is SQL Server not Access. Access does not have a built in *smsg*. Are you planning on installing SQL Server?
2. Since you state that Access id not being used as the Frontend what is?
 
GinaWhipp said:
So, a couple of things here...

1. That is SQL Server not Access. Access does not have a built in *smsg*. Are you planning on installing SQL Server?
2. Since you state that Access id not being used as the Frontend what is?
Click to expand...

Hi GinaWhipp

Thanks for replying.


  1. Even though that's the case, is the premise not transferrable? (Not a rhetorical question!)
  2. An existing VBA app within Excel is what contains forms that will send the information into the database. Another form will be used to view the data.
Next steps? :D
 
I think if you add a few fields to your Message table you can make the whole thing simpler.

First and definitely the MessageArchive table should go--it breaks normalization. You shouldn't have 2 tables with the same structure. When you do, you are essentially storing data about each record in the TableName (Archived). So instead you create a field in Message to determine if a message is archived or not. With that said, I don't really see the point of what archiving a message will do. How are archived messages going to be handled differently than regular messages? Users are still going to have access to them right? So what's the point of designating messages as such?

Second, I believe you can elminate MessageThread by putting a ParentId field in the Message table. That will tell you what message a message is in response to. A completely new message will not have a parent.

So, I believe this should be your Message table:

MessageID
Subject
Body
FromUser
ToUser
DateCreated
MessageRead
Archived
ParentMessageID

That gets you down to 2 tables (Messages & Users). and has all the functionality you curretnly have.
 
1. THAT premise is not *transferrable*. That said, you could roll out your own... if it were me, I'd choose to use SQL Server or Outlook as the time it would take to create would far out weigh the benefit. In other words, let's say it took 40 hours of *hard labor* to develop, SQL Server has a free version were it's built in. Get my point? (No one wants to reinvent the wheel.)

2. Hmm, then perhaps you should consider using .NET or C#? Mind you not very often I *hand slap* folks away from Access but in this case I'm just not sure it's the *best tool* for the job, not by itself that is.
 
Access is not the right tool. How about using a web-based email service?

I can't think of any good reason to develop your own messaging service. No disrespect but if you don't have something else more useful to do with your time then I think I would start looking for a new job.
 
I agree with Buttonmoon --look for a web-based service that exists and is supported. Why build your own application?

jr007, you have mentioned Access and forms in excel and are working with a SQL server tutorial.
I suggest you focus on WHAT you are being asked to do, and look for feasible options.
I find it strange that people in your organization would have Access (and possibly Excel) but no Outlook.

Perhaps if you told us the requirements in clear and simple English there may be some options or suggestions. You seem to be in a technical maze with the current line of thinking.

Good luck with your project.
 
Hi folks

Thanks for the responses (rebukes?) :)

On a serious note, the restrictions I've stated are there. Whether it be strange or not doesn't change anything...

This tool is nothing really much more than a trial to link multiple areas by providing a basic tool to communicate with. As it stands, web-services and SQL server are not options that are on the table (until this "proof of concept" has been accepted).

As such, my requirement is as it was before:

- Using an existing basic application, link a database that will allow the exchanging of messages (allowing replies to be linked).

I'm all for feasible options based on the restrictions in place, and will happily take direction where progress can be made.

Based on plog's solution above (which seems fairly straightforward), should I interpret what else is being said as plog's recommendation being unviable?
 
Last edited:
plog said:
How are archived messages going to be handled differently than regular messages? Users are still going to have access to them right? So what's the point of designating messages as such?

Second, I believe you can elminate MessageThread by putting a ParentId field in the Message table. That will tell you what message a message is in response to. A completely new message will not have a parent.

So, I believe this should be your Message table:

MessageID
Subject
Body
FromUser
ToUser
DateCreated
MessageRead
Archived
ParentMessageID

That gets you down to 2 tables (Messages & Users). and has all the functionality you curretnly have.
Click to expand...

Thanks very much for the helpful response plog.

With the MessageArchive table, the rationale there was to move messages from the 'Messages' table to this table after a period of time (or maybe if an additional 'Status' field were added where you can 'Close' down the message, and therefore move it to another table). The reason being, to help performance when reading/writing to/from the Messages table (closed / old messages would rarely be reported on).

Just one more question as well plog: if there's a ParentID, do I not need another 'child' table to connect to this? Or is this 'ParentID' a field that will be left as 'Null' for those initial (new) messages, and that only is updated when a reply is submitted?

Thanks again for your help.
 
Unless you are talking about 100's of thousands of messages don't bother with the separate archive. You have a message date field you can filter on , and if you wanted to simplify your queries and forms you could add a Archived flag to the message, and not bring those in.
 
You wouldn't need an additional 'child' table. The Messages table would act as that.


In a query, you would daisy chain instances of Messages together. The ParentMessageID in one instance would link to the MessageID of another instance.

The problem with this is the deeper you want to retreive messages the more intensive the query. For example, message 107 is the child of message 59 which is the child of message 44 which is the child of 35 which is...the query to retrieve message 35 from message 107 is pretty onerous.
 
plog said:
For example, message 107 is the child of message 59 which is the child of message 44 which is the child of 35 which is...the query to retrieve message 35 from message 107 is pretty onerous.
Click to expand...

Thanks plog.

Would the query have to act in that way? Could the same results be achieved by looking for anything with the parent ID, which is then sorted by date/time to get the most recent?
 
Hmmm.
This tool is nothing really much more than a trial to link multiple areas by providing a basic tool to communicate with. As it stands, web-services and SQL server are not options that are on the table (until this "proof of concept" has been accepted).

As such, my requirement is as it was before:

- Using an existing basic application, link a database that will allow the exchanging of messages (allowing replies to be linked).

I'm all for feasible options based on the restrictions in place, and will happily take direction where progress can be made.
Click to expand...

How are messages going to be delivered to intended recipients?
If you go with SQL Server Express (based on Gina's info), that has messaging built in, what software would you use for development? If it's new software to you, and a new product to your company, is that a feasible option?
If you have Access and Excel - I'll assume Word, Powerpoint ...(most of Office), is it feasible to install Outlook? Seems feasible, but you know the details.

If everyone has a networked PC and a browser, then a web-based product would also seem a feasible option.

If you are bound and bet to use Access, and it is, as you say, a proof of concept, then the following info may be useful to you.

There is a simple message approach described in the excerpt from a book "Beginning Access 2002 vba". It is NOT an application, and may not meet any of the criteria you have for such an application.
The approach is an example to show the importance of Event programming and Raising an event. It deals with a Class module and 2 forms. Each form has 2 text boxes (txtSend and txtReceive) and code to interact with the Class. If you enter a "message" into txtSend on Form1, immediately after entering the message, it shows up in the txtReceive of Form2. It is a basic broadcast.

Depending on your proof of concept, and your coding skills/interest, you could adapt this to send/receive messages from individuals or groups. Further, you could log all messages with timestamps to a file/table. And even further, you could identify messages by type/content/descriptors to do analysis of message subjects or relevance to some factor(s) important to your company.

See chapter 14 page 594 in the excerpt at this link

As before, I suggest you determine exactly what you need and what is feasible.

Good luck.
 
I think that's the right line of thought, but now that you say that I think you do need a MessageThread table to implement it. So here's my new thought:

3 tables--Users, Messages, Threads

Threads
ThreadID, autonumber primary key
ThreadDate, date, date thread was created
ThreadArchived, yes/no, determines if thread is 'archived'

Messages
MessageID, autonumber primary key
ThreadID, number, foreign key to Threads
Subject
Body
FromUser
ToUser
DateCreated, date/time--need to include time in all messages
MessageRead

So, whenever a completely new message is created it creates a record in both Messages and Threads. When subsequent messages are made in the thread those just add records to Messages and use the existing ThreadID. Then you just have to sort messages by DateCreated to get them all in order.

Additionally, this makes it simple to include multiple users on a thread instead of just 2 people. For that you would remove the ToUser from Messages and make a new table called something like ThreadUsers which would contain the ThreadID and then each UserID for everyone who is on the thread.
 
You must log in or register to reply here.

Similar threads

Snowflake68
Catch Error - Mapped Network drive
Replies
17
Views
222
The_Doc_Man
The_Doc_Man
H
Generate a table with a list of numbers between the given range
Replies
11
Views
460
Uncle Gizmo
Uncle Gizmo
J
The Deep State
6 7 8
Replies
149
Views
6,756
Steve R.
Steve R.
M
Solved A lookup table, attempted referential integrity, and the puzzle of the no unique index error
Replies
6
Views
775
Missomissou
M
mloucel
OpenArgs issue..
Replies
5
Views
200
mloucel
mloucel

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom