|
I'm creating a database that can send private messages. The system utilized MSSQL database engine and uses relations to constrain the data inserted.
Problem is that users can send mass-messages and that can add up quickly if each row in a table represents a message to a user. My question is: Is it better use one row per message or use a comma separated list of users?
I would think that the table with the ID's of the message and users (many-to-many relation table) can become REALLY large quickly. A comma separated list would keep the number of rows down.. What is more effective?
|
|
|
|
|
assuming that the recipient list are also users
User Table Columns
UserID+User
Message Table Columns
MessageID+Message
Sent Table Columns
UserID+MessageID (using the senders user id)
Receive Table Columns
UserID+MessageID (using the recipients user ids)
use csv if recipients are not users or you don't need to track
don't use csv if you intend to parse later - not effective
|
|
|
|
|
Your proposal is actually very close to my current database schema. As for the CSV, If the users need to delete a mass-message, I need to keep a list of unique receivers. This can be represented by rows or CVS. I need to edit the CVS in case a user delete a message. I need to remove a row in case I use rows.
It is simply a matter of what is the most effective method after a (long) period of time. I would guess that a table with >1.000.000 rows would take a long time for the database to process.
Then I rather have to use a little more memory and CPU time on parsing a comma separated list.
Any experiences on this matter? Any good db schemas I can look at? (I'm thinking forums here).
|
|
|
|
|
Yes, lots of experience. Just finished a db optimization contract(job). The client was adding a million records every couple of days. Have been doing the same for 20+ years.
The Receive Table may have a lot of records in it but only 2 columns and those columns will make up the primary key. To query for a given record, you will use the pk. Clustered, indexed, primary key 2 columns only - should be able to get any given record in about 3 milliseconds or less with 10 million records in it. Subtract a ms or 2 if your id values are numeric. This type of table will not take up much space.
This will be thousands of times faster than searching for a partial string value in text where you will won't be able to find the user by using an index - at all. (not easily anyway)
Searching for a string in a text type field is a VERY time consuming operation for a db to do - not a good idea.
I would create test scripts and data to validate if you're not convinced. Let me know if you need help.
What db are you using?
|
|
|
|
|
Sounds great. I don't have a lot of experience on the inner workings of SQL databases, so I was not sure what the best approach was. I will build the database without CSV and hope the database is clever enough to find a single (or range of) row within millions of rows without any performance problems.
The current database schema works exactly like that (2 PK) on numeric datatypes.
And you are right, somehow I did not take the partial text matching into account. That would take ages. That alone would keep me from using CVS.
As a side note, the database is a MSSQL 2008 Express engine. Thanks for your support. I really appreciate it.
|
|
|
|
|
If your problem is the number of rows, one million rows is not large for a modern RDBMS. If it is indexed properly, it will cope with that with absolutely no problem in terms of simple queries or updates.
Things get a bit hairier if you're doing more complex manipulations, using cursors or anything like that, or doing wildcard queries like '%XYZ%', that sort of thing.
|
|
|
|
|
The database schema is created in such a way that data that needs complex queries are placed in their own tables. The table in question is a 2 column composite key table. From what I've gathered here, the engine should have no trouble doing simple queries against several million rows.
Thanks for the clarification though. Does any of you by chance have any good articles on database optimization and inner workings of RDBMS?
|
|
|
|
|
I have written a small medical research application is asp that uses a sqlserver database to store data patient research data. To set the scene for my question; I am not sure what the term of the table/field design I have used is but I will try to explain.
I have a table that I have called ref_FieldDefinition, this contains the definition of each data field I need to store patient research data for. (E.g. FieldUID, FieldLabel, FieldType, etc.). So I could define a field for example FieldLabel ="Smoker", with FieldType="T/F".
I then have a seperate table that stores the data for each defined field , data_PatientData, (PatientUID, FieldUID, StoredData). (This is a simplified description of how I am constructing the db).
When I present a user with an asp page for a particular patient, the page is built based on which fields are defined in the ref_FieldDefinition table and then which data is applicable from the data_PatientData table. Hence, I have no hard wired fields in my table design.
This concept works fine for classic database field definitions, where you have a fields label and a field data input object (like a textbox, dropdown, checkbox, etc).
Yesterday I was asked could I create a 3x3 "table" on the screen for users to enter values into. The first two rows of the table will contain numbers and the bottom row will contain the sum of each column. If I had a classic db table design I could hardwire fields like "topleft", "topmiddle", "topright" etc.
I was hoping to do this in a smarter way and somehow define a "table" type field that I could dynamically build on display, as I do with my other field definition.
Am I aiming too high here ? Does anyone have any thoughts on this ? Some brainstorming would be much appreciated.
Cheers
Ryan
|
|
|
|
|
I use the same design structure except I call then attributes and attribute types and link them to all sorts of things via a many to many link table.
I use SQL 2008 so I make heavy use of the pivot functions. Only problem is the columns are dynamic so you need to use dynamic sql. This article may interest you Pivot two or more columns in SQL Server 2005[^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am using an application created by a vendor where they use XML tables to store "custom" grid type data that we can define.
For example:
Given an employee record, we want to store safety qualifications and their expiration dates. Something like "CPR", "QualifiedOn", "ExpiresOn"
What the vendor has done is created an additional table called,
"EMP_MATRIXDATA"
KEYID
MATRIX_NAME (name we give the custom grid, like "SafetyTests"
MAXTRIX_DATA (datatype is an XML document)
With this type of design, you can provide a very flexible method for storing all kinds of grid data. You can even build in an XSD column into the table which would hold the schema of the associated table.
You will pay a price for searchability, but do some research on the topic of storing XML data in SQL server and you will be suprised how good it really is.
Good luck.  
|
|
|
|
|
I have a columns say, Col and Col[1] in table say Test. Now I want to do a select from it. How to go about it? (Since [] has a special meaning for SQl server) I tried
select Col,Col[1] from Test
but for obvious reasons, it does not works.
[Edit]
This will work:
Select Col,[Col[1]]] from Test
[/Edit]
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
modified on Thursday, December 17, 2009 12:59 AM
|
|
|
|
|
try
select Col,[Col[1]] from Test
Who would be dumb enough to use reserved words or characters in a column name. Stoopid really.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: Who would be dumb enough to use reserved words or characters in a column name.
Well, I just did that to find how SQL will behave.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
Did not even look at the moniker before answering otherwise I'd have been questioning your sanity.
Doing that is akin to sticking your finger in the power point, may not kill you but your fingers gonna sting for a while.
So did the extra set of brackets work? I flatly refuse to try it out
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: Doing that is akin to sticking your finger in the power point, may not kill you but your fingers gonna sting for a while.
 Never tried this.
Mycroft Holmes wrote: So did the extra set of brackets work?
Yes it did.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
Dear All,
First i would like to sorry if my expliant not clear.
My issue is that
I want group data in field company name which not equal each other.
It caused by inputting different users.
Example:
Company_Name
ComxxxA Co.
ComxxxA Co.
ComxxxA Co.,Ltd.
ComxxxA Co.,Ltd.
ComxxxA Co.,
ComxxxA Co.,
ComxxxA
ComxxxA
Comx
ComxxxxxxxB Co.,Ldt
ComxxxxxxxB Co.,Ldt
ComxxxxxxxB Co.,Ldt
ComxxxxxxxB Co.,Ldt
ComxxxxxxxB Co
ComxxxxxxxB Co.,
ComxxxxxxxB Co.,
..... (there r alot company name which difference)
So up to example above, what i want is the name is shortest
ComxxxA
Comx
ComxxxxxxxB Co
thanks for your help...
VB.Net
|
|
|
|
|
Try this
declare @t table(Company_Name varchar(50))
insert into @t
select 'ComxxxA Co.' union all select 'ComxxxA Co.' union all
select 'ComxxxA Co.,Ltd.' union all select 'ComxxxA Co.,Ltd.' union all
select' ComxxxA Co.,' union all select 'ComxxxA Co.,' union all
select 'ComxxxA ' union all select 'ComxxxA ' union all
select 'Comx' union all select 'ComxxxxxxxB Co.,Ldt' union all
select 'ComxxxxxxxB Co.,Ldt' union all select 'ComxxxxxxxB Co.,Ldt' union all
select 'ComxxxxxxxB Co.,Ldt' union all select 'ComxxxxxxxB Co' union all
select 'ComxxxxxxxB Co.,' union all select 'ComxxxxxxxB Co.,'
Query:
select Company_Name
from @t
where replace(Company_Name, ' ','') not like '%[.,]%'
group by Company_Name
Output:
Company_Name
Comx
ComxxxA
ComxxxxxxxB Co
Niladri Biswas
|
|
|
|
|
Thanks,
Yes it like example.
But it is not enough yet.
Because in this field has more companies like that
up to differenc user enter difference.
So have any solution for protect it ?
VB.Net
|
|
|
|
|
|
it is not complete what i want.
but i would like to thank to you so much.
VB.Net
|
|
|
|
|
Hi,
you can use SELECT DISTINCT[^] and ORDERBY to get an ordered list of all different company names present.
you can't find the "shortest" names as in your example, it would only give "Comx", not what you said it would.
|
|
|
|
|
Hi.
I wrote some VBA underlying code for an Access 2000 application and used to create/open recordsets in the following manner:
Dim rstLookupFixedAreas As New ADODB.Recordset
rstLookupFixedAreas.Open "tbl_FixedAreas", CurrentProject.Connection, adOpenStatic, adLockOptimistic
rstLookupFixedAreas.MoveFirst
Do Until (rstLookupFixedAreas.EOF)
strValueList = strValueList & CStr(rstLookupFixedAreas.Fields(0)) & ";" & rstLookupFixedAreas.Fields(1) & ";"
rstLookupFixedAreas.MoveNext
Loop
comboFixedArea.RowSource = strValueList
'comboFixedArea.DefaultValue = 1
rstLookupFixedAreas.Close
I did this by including the reference to Microsoft ActiveX Data Object 2.1 Library.
Now that I am using Access 2007, is there a more current method to do the same type of "recordset" operations? (or should i continue with including ADO 2.1 Library)
I want to use either a record index or Find method to get my record pointer.
Thank you for the help.
John John
|
|
|
|
|
I have a .csv file with following column:
Col1,Col2
And the data table has one extra datetime column:
Col1,Col2,TimeStamp
While importing data using BULK INSERT from the CSV, is there a way to specify the value for the TimeStamp column too? (Timestamp would be same for all the newly imported rows)
Suhredayan
|
|
|
|
|
How about defining a default value for the column?
ALTER TABLE dbo.tablename ADD CONSTRAINT
DF_TimeStamp DEFAULT '20091218' FOR TimeStamp
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Ashfield wrote: How about defining a default value for the column?
That may not work since the timestamp is same only per bulk insert. I do have a solution for this, wrap bulk insert inside a stored proc, and update the the timestamp from SP. But this would introduce few additional steps, was wondering if this can be avoided in case if BULK INSERT already has this feature.
Thank you,
Suhredayan
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
