|
So im designing a pm system for a game written in Ruby/On Rails and not quite sure how i would design the database side of the game to facilitate the desired features. The general message system looks like
Table: users
int userid
~ extra
Table: messages
int messageid
int authorid
longtext message
datetime timestamp
Table: message_to
int key
int messageid
int recieverid
What I'm looking to add is essentially custom folders. Meaning a user could create a "Diplomatic" folder and have the message appear under that folder. How would one go about facilitating the association between a message and a folder?
|
|
|
|
|
You need to create a table for folders i.e.
Table: folders
int folderid
text name
and then in meesages table add
int folderid.
Then when you access a message you know which folder it belongs to.
|
|
|
|
|
Yes but that doesn't hand ownership of folders would one do
Table: Folders
int folderid
varchar foldername
int userid
the do something like
SELECT foldername FROM table WHERE userid="x";?
then look into messages with
SELECT message, folderid FROM table WHERE receiverid="x";
then output based on folder?
|
|
|
|
|
Sorry, didn't get the part about folder ownership. This how I would set it up
Tables:
folders:
id int
name varchar
userid int
messages:
id int
fromid int
toid int
message varchar(250)
messagedate datetime
folderid int
users:
id int
name varchar
Then when you want to look up all messages from one person (say his id is 1) to another person (id = 2) in a particular folder (say id = 1) you would have a query like this
SELECT
m.message
FROM
messages m
INNER JOIN
folders f
ON
m.folderid = f.id
WHERE
m.fromid = 1
AND m.toid = 2
AND f.id = 1;
|
|
|
|
|
thanks alot, actually the first table gets inserted, but the rest do not.
thanks.
|
|
|
|
|
Hi,
i have this stored prcedure to update multiple tables in sqlserver DB,
when i run it , i get an saying tbpassport,tbaccounts, tbpermanentaddress and tbcontactaddress cannot allow nulls.
this is the code, where am i getting it wrong.
CREATE PROCEDURE InsertEmployeeRecord
@emp_id char(5), @surname char(25), @fisrtname char(25), @middlename char(25),
@sex char(10), @date_of_birth char(12), @ministry_code char(3), @dept_code char(3), @nationality char(15),
@state_code char(3), @lga_code char(3), @designation char(15), @date_of_employment char(12),
@unit_code char(3), @employement_type char(15), @marital_status char(10), @religion char(10), @glevel char(10), @step char(8),
@picture image,@filename char(52), @bank_name char(52), @bank_code char(3), @sortcode char(9), @branch char(100), @account_no char(40),
@pcity char(45), @ptown char(45), @pstreet char(100), @pphone char(200),
@ccity char(45), @ctown char(45), @cstreet char(100), @cphone char(200)
AS
BEGIN
SET NOCOUNT ON
INSERT tbemployee (emp_id,surname,firstname,middlename,
sex,date_of_birth,ministry_code,dept_code,nationality,
state_code,lga_code,designation,date_of_employment,
unit_code,employment_type,marital_status,religion,glevel,step)
VALUES(@emp_id, @surname, @fisrtname, @middlename, @sex, @date_of_birth, @ministry_code, @dept_code, @nationality,
@state_code, @lga_code, @designation, @date_of_employment, @unit_code, @employement_type, @marital_status,
@religion, @glevel, @step)
INSERT INTO tbpassport (emp_id,filename,picture)
VALUES(@emp_id, @filename, @picture)
INSERT INTO tbaccounts (emp_id,bank_code,bank_name,sortcode,account_no,branch)
VALUES (@emp_id, @bank_code, @bank_name, @sortcode, @account_no, @branch)
INSERT INTO tbpermanentaddress (emp_id, city, town, street, phone)
VALUES (@emp_id, @pcity, @ptown, @pstreet, @pphone)
INSERT INTO tbcontactaddress (emp_id,city,town,street, phone)
VALUES (@emp_id,@ccity, @ctown, @cstreet, @cphone)
END
GO
thanks.
|
|
|
|
|
It seems as though your tables have been set up not to accept null values. You either need to edit the table to accept nulls (probably not the best approach), or check whether your input values are not null before running the stored procedure(my preferrred approach) .
|
|
|
|
|
you kind of lost the thread there with your second post, however..
Either change the tables to allow nulls,
Or ensure no null values are passed to the procedure,
Or best of all, use isnull checks for each insert.
e.g.
INSERT INTO tbcontactaddress (emp_id,city,town,street, phone)
VALUES
(
isnull(@emp_id, '')
,isnull(@ccity, '')
,isnull(@ctown, '')
,isnull(@cstreet, '')
,isnull(@cphone, '')
)
Another consideration is that rather than passing so many parameters you may be better off passing it all wrapped up in a single XML parameter.
|
|
|
|
|
select spid, status, loginame, hostname, blocked, db_name(dbid), cmd from master..sysprocesses
Above query lists all hosts currently logged in using which login name.
But i want which hostname have logged in past using which login name . where do i found this information ?
Is there any log is maintained for that.
|
|
|
|
|
Member 6392036 wrote: i want which hostname have logged in past
AFAIK, the information isn't stored historically. If you want to know who was online when, then you'll need to add some kind of logging.I are Troll 
|
|
|
|
|
CREATE PROCEDURE sp_Get_Borderel_4d_data
@MaatschappijID numeric,
@VervalDatum datetime,
@LanguageID char(1)
AS
DECLARE @MaatschappijIDLoc numeric
DECLARE @VervalDatumLoc datetime
DECLARE @LanguageIDLoc char(1)
SET @MaatschappijIDLoc = @MaatschappijID
SET @VervalDatumLoc = @VervalDatum
SET @LanguageIDLoc = @LanguageID
SELECT
Agent.AgentID,
Agent.Naam AS AgentNaam,
ProRataDetail.InternPolisNummer,
(sum(ProRataNettoPremie) - sum(ProRataCommissie)) AS Techpremie,
sum(ProRataNettoPremie) As ProRataNettoPremie,
sum(ProRataTaksen) As ProRataTaksen,
sum(ProRataBrutoPremie) As ProRataBrutoPremie,
sum(ProRataCommissie) As ProRataCommissie,
sum(ProRataBeheerskosten) As ProRataBeheerskosten
FROM
fn_Get_ProRataDetail(0, '{00000000-0000-0000-0000-000000000000}', 0, 0, @MaatschappijIDLoc, @VervalDatumLoc, @LanguageIDLoc)
as ProRataDetail
LEFT JOIN Polis ON Polis.PolisID = ProRataDetail.PolisID
LEFT JOIN Klant ON Klant.KlantID = Polis.KlantID
LEFT JOIN Agent ON Agent.AgentID = Polis.AgentID
WHERE
Polis.Demo <> 1
GROUP BY
Agent.AgentID,
Agent.Naam,
ProRataDetail.InternPolisNummer
ORDER BY
Agent.Naam,
ProRataDetail.InternPolisNummer
GO
----------------------------------------------------------
CREATE PROCEDURE sp_Get_Borderel_4d_data
@MaatschappijID numeric,
@VervalDatum datetime,
@LanguageID char(1)
AS
SELECT
Agent.AgentID,
Agent.Naam AS AgentNaam,
ProRataDetail.InternPolisNummer,
(sum(ProRataNettoPremie) - sum(ProRataCommissie)) AS Techpremie,
sum(ProRataNettoPremie) As ProRataNettoPremie,
sum(ProRataTaksen) As ProRataTaksen,
sum(ProRataBrutoPremie) As ProRataBrutoPremie,
sum(ProRataCommissie) As ProRataCommissie,
sum(ProRataBeheerskosten) As ProRataBeheerskosten
FROM
fn_Get_ProRataDetail(0, '{00000000-0000-0000-0000-000000000000}', 0, 0, @MaatschappijID, @VervalDatum, @LanguageID)
as ProRataDetail
LEFT JOIN Polis ON Polis.PolisID = ProRataDetail.PolisID
LEFT JOIN Klant ON Klant.KlantID = Polis.KlantID
LEFT JOIN Agent ON Agent.AgentID = Polis.AgentID
WHERE
Polis.Demo <> 1
GROUP BY
Agent.AgentID,
Agent.Naam,
ProRataDetail.InternPolisNummer
ORDER BY
Agent.Naam,
ProRataDetail.InternPolisNummer
GO
--------------------------------------------------------------------------
Why is the first procedure, faster than the second?
The ony difference are the extra local variables!
thanks,
A.
|
|
|
|
|
The question is why are you using 3 extra variables, that takes 3 extra cycles and therefore it is slower  Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I can run it a 1000 times!
the difference is:
the first one with local variables: I get a result in 5 seconds.
the second one without local variables: I get a result in 20 minutes!!!!
Thank you
|
|
|
|
|
This looks like a problem caused by Parameter sniffing. Just google on it and you'll see what I mean.Wout Louwers
|
|
|
|
|
thank you all,
it has something to do with Parameter Sniffing!
It explains a lot!
Is this the best practice? to disable parameter sniffing?
Thank you
|
|
|
|
|
Well,
You are using the parameters in the call to fn_Get_ProRataDetail. You could try what happens ig you do the local var trick in that function. That way other sp's may benefit too. But the local var trick is the only one I know besides using With Recompile.Wout Louwers
|
|
|
|
|
See thats the reason I spend time here, never heard of parameter sniffing, sounds disgusting. There are some excellent articles out there, another tool in the ongoing fight for performance.
Thanks Wout.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm not sure if this is the best place to post this, but I'm retrieving a table form an SQL database and databinding the contents into a DataGridView.
I have some code that searches the entire DataGridView and locates null values, and I intend to color the fields red so the user knows he needs to put something in.
private void button1_Click(object sender, EventArgs e)
{
for (int column = 0; column < 1; column++)
{
for (int row = 0; row < this.dataGridView.RowCount; row++)
{
if (this.dataGridView.Rows[row].Cells[column].Value == null)
{
this.dataGridView.Rows[row].Cells[column].Style.BackColor= Color.Red;
}
}
}
}
theres still some messageboxes in there that I used to figure this out...
If I change the conditional statement to (this.dataGridView.Rows[row].Cells[column].Value != null) so that it searches for non null fields, it successfully colors the cells.
I guess my problem has to do with the nullity (lol is that a word?) of my cells.
I guess I should include how I am populating my dataGridView...:
private void Form1_Load(object sender, EventArgs e)
{
this.pickups___CarsTableAdapter.Fill(this._Vehicle_EquipmentDataSet._Pickups___Cars);
}
that was auto generated =)
Perhaps I can handle the null data when the list is populated?
Thanks in advance!!!modified on Thursday, March 18, 2010 12:45 PM
|
|
|
|
|
Mattzimmerer wrote: this.dataGridView.Rows[row].Cells[column].Style.ForeColor = Color.Red;
how do you see the ForeColor of an empty cell?
|
|
|
|
|
Ohh, i should have changed that back. No I first tryed backcolor or w/e, which works find for populated cells... that was a purley desperate attempt..
THE PROBLEM PERSISTS!!!!
|
|
|
|
|
You should check the value from the data source itself, not datagridview's cells. Subscribe to the datagridview's CellFormatting event, then in the handler get the DataBoundItem (object, datarow etc) and set color base on the DataBoundItem's data itself.
I'm binding to collection of object, btw, so YMMV.
private void dataGridView_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{
string dataPropertyName = this.dataGridView.Columns[e.ColumnIndex].DataPropertyName;
ObjectType theObject;
switch (dataPropertyName)
{
case "ColumnName":
theObject = (ObjectType)this.dataGridView.Rows[e.RowIndex].DataBoundItem;
break;
}
}
|
|
|
|
|
Fixed my problem:
private void Null_check_Click(object sender, EventArgs e)
{
for (int row = 0; row < this.dataGridView.RowCount-1; row++)
for (int column = 0; column < this.dataGridView.ColumnCount; column++)
if (this.dataGridView.Rows[row].Cells[column].Value.ToString() == "")
this.dataGridView.Rows[row].Cells[column].Style.BackColor = Color.Red;
}
just needed sleep I guess...
|
|
|
|
|
CREATE TABLE [PreferenceType] (
[PreferenceTypeId] [BIGINT] IDENTITY (1,1) NOT NULL,
[LongName] [NVARCHAR](500),
[ShortName] [NVARCHAR](250)
);
CREATE TABLE [Table2]
(
[Table2Id] [BIGINT] IDENTITY (1,1) NOT NULL,
[PreferenceTypeId] [INT]
)
Given the Preference Lookup table above, if I do an Inner Join to Table2 on the PreferenceTypeId field, I should index like this:
CREATE INDEX [PreferenceTypeId_Idx] ON [Table2]([PreferenceTypeId]); <--- good?
But I shouldn't index like this:
CREATE INDEX [PreferenceTypeId2_Idx] ON [PreferenceType]([PreferenceTypeId]); <--- bad?
because Primary Key fields should already be indexed, correct?
|
|
|
|
|
You should make PreferenceType.PreferenceTypeId a PrimaryKey (Yes it will be automatically indexed) and you should make Table2.PreferenceTypeId a ForeignKey constraint referencing PreferenceType.PreferenceTypeId (this will also be automatically indexed).
[Edit]
Foreign keys are NOT automatically indexed.modified on Wednesday, March 17, 2010 5:06 PM
|
|
|
|
|
KEWL, i learned something new, that foreign keys automatically get indexed for me.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
