Database Design Help

[Ctwizzy]

I have 2 databases, one which hosts employee information called empInfo.
And the second hosts employee times empTime.

Now I am also adding in the ability to leave users a Message so when they punch in they get a message. Now this is easy, as I could just add in a message field to the empInfo.

But I need to be able to archive the messages, and not only that be able to leave a message to a user called ALL (which would be a global message not dependant on a user).

So I would need a new table call this one empMessage.
Now it has to have a link to the primary table (empInfo) as it should only have messages to users that exist(so refferentiall integrity), but ALL does not exist (unless i make a generic user ALL which may be the solution).

So how do I make sure I have ONLY users from the empInfo table yet be able to have global messages?

I hope this makes sense.

My solution is to create a ALL user in the empinfo table, and then when displaying the message query for all, and do individual queries for the other users.

Thanks!

[antares686]

First off names of tables should be plural no matter what else you do with them as they represent more than one object.

But as for your question it depends on the type of database system you are using.

Most database systems have available triggers.

A trigger is a piece of SQL designed to execute based on a condition.

Ex in MS SQL Server

CREATE TRIGGER Validate_Reference ON dbo.empMessages
FOR INSERT, UPDATE
AS

SET NOCOUNT ON

IF EXISTS (SELECT 1 FROM inserted WHERE colFK NOT IN (SELECT colPK FROM dbo.empInfo) AND colFK <> 0) -- Whatever you codition for ALL is
BEGIN
RAISERROR('Ooops',16,-1)
ROLLBACK TRAN
END

Basically you enforce referential integrity the way it was done before the FK constraint came along.

[erickwidya]

i think u don't have to use the All user for that..

EmpMessage = EmpID + EmpMessage + MessageTo + ...
Employee = @EmpID + ...

so with that u have 1 to many relation.
MessageTo can be 'EmpId' or maybe 'All' for All user

So how do I make sure I have ONLY users from the empInfo table yet be able to have global messages?

u can check it via Code to keep the Integrity.

[Ctwizzy]

antares686 - sorry but Im real new to DB stuff and only know very simple queries triggers and the such fly way over my head.

erick - are you saying to use an "All" user or what im not sure.

Thanks!

[erickwidya]

something like this

Employee = @EmpId + EmpName + ...
er + Erick
Ct + Ctwizzy

EmpMessage = EmpID + EmpMessage + MessageTo + ...
er + "Hello there" + All
er + "Hi Ct" + Ct

so the prog will display the EmpMessage if MessageTo is match with EmpID (in this case Ct) and/or MessageTo = All

PS : All is not 'All User'..is just like a 'Flag' that describe to where the Message is sent

hope u understand

[Ctwizzy]

Ok so your saying create a new Table with
Emp Name | Message | To

Emp Name = Their Emp Name
Message = the Message
To = All or User

where a sample answer could be
Ctwizzy | Hello! | user

ok this is fine but, a message to Ctwizzy shouldnt have the option of all he has been selected from the list.

I still think this wont be possible without an all user.


Thanks!

[Ctwizzy]

what about creating a table JUST for messages to everyone?

Or maybe just store the messages in a file? Not to sure anymore.

[erickwidya]

...a message to Ctwizzy shouldnt have the option of all he has been selected from the list.

not quite understand, can u explain a bit?

I still think this wont be possible without an all user.

so tell us why u think it need "All" User? we can learn something from u too (i know i would)

what about creating a table JUST for messages to everyone?
Or maybe just store the messages in a file? Not to sure anymore

not a good solution, but there's still possibility for that..

PS : r u thinking for designing a table that related to other (in this case "TO" at New table and "EmpId" at Employee table?)
i'm not a database expert, but i think u can't do the 'whole' at RDBMS, u can use 'VB Code' for that..

regards

[Ctwizzy]

Your saying that I have EmpID, Message, To fields

All users

 insert into MessageTable (EmpId, Message, To), values("Ctwizzy", "Testing message", "All");

or

single user

 insert into MessageTable (EmpId, Message, To), Values("Ctwizzy", "Testing message", "User");

This is how I see you say to do it.
Problem with this is:

UI:

User ------------ OR All Users

----------------- ------------------
----------------- ------------------
----------------- ------------------
----------------- ------------------

Submit

Now your saying that I should scrap the right hand side and use the left only, so say pick ctwizzy but set 'To' field to All and message is for all. But that means im using that username to set an all message.



The reasoning for an All user, is I can just insert into X where name=All message=blah.

Just looks like it would be the easiet solution now.

[erickwidya]

...so say pick ctwizzy but set 'To' field to All and message is for all. But that means im using that username to set an all message.

yes, so u using EmpID(Ctwizzy) that send Message to All Employees, isn't that what u want (to be able to trace the message)?

The reasoning for an All user, is I can just insert into X where name=All message=blah.

and after that, what u planning to do?

sorry if i asked like this..it's for me to understand what u trying to do

[Ctwizzy]

No tracing is not a factor. Its a windows app. Only admins can get into the leave a message feature. They can leave global messages for instance when the app first starts, or can leave personal messages to individual users like they they log on when using the app.

Hence why im thinking the All user would be a good idea?

[erickwidya]

well this what i thinking..the rest i'll leave it up to u

i not use "All User" and not use All 'flag' like i recomended before..
the Message table looks like this
1. EmpMessage = EmpID + EmpMessage + MessageTo + isRead + ...

and at ur program u can do something like this
u first add Combobox which contain value like this 'All', and (Emp1, Emp2, ... the list goes on depend on Employee table)

so at code u check if the Combobox.Listindex = 0 (means the message is to All), it'll save to EmpMessage Table like this
(i assume u have 3 Employee call Ctwizzy, emp1, emp2 and Ctwizzy send message to All)

Ctwizzy + "hi to all" + emp1 + False
Ctwizzy + "hi to all" + emp2 + False

and if the emp1 is login, it'll display the message and put True for the isRead field and so on for personal message too

good luck

[Ctwizzy]

Thanks erick.

Sorry i havnt replied sooner im suffering from strep atm.

Anyways I see where your coming from, BUT your still needing someone to send the message. Which would be fine if I cared who sent the message but I dont.

Plus if what I understand your saying is that id be sending the same message to all employees, so that if i had 40 emps theyd all have the message "Hello" this is a waste of space, why record the message 40 times when I can record once?

Im still looking for a better solution to the "all" user.

Thanks!

[erickwidya]

...why record the message 40 times when I can record once?

yes i'm aware of that, but i can't figure it out how to make prog know whether the message already display to specific user or not (if it's an offline message) but of course that's got to be better method..plz post if u found one ok

good luck

PS:get well soon