Hello to all out there
I want to design a database for an Email system.
Two options are coming to my mind
1) Whether I will go with a separate table for each user which will contain in each field information like MSGid, recipient, sender, subject, message.
2) or I will maintain the information in a single table for each user.
I am not able to comprehend the pros and cons of each solution above.
Please help.
Hi Guys,I've been thinking about a normalised schema for a folksonomy syetem for my site. I'm fairly sure this is a good way to do things, but I have a coupld of questions... firstly, here's a quick low down on planned schema: Usersuser_idetc.Itemsitem_iditem_descetc.Tagstag_idtag_textItemTagstag_iduser_iditem_id Okay, now here's the thing. Obviously this is quite a normalised schema. I can retrieve all of an item's / user's tags easily. What I'd need to be able to do is return the list of tags and count for each item (perhaps just an item at a time).I can't really see any problems with this right now. But what happens if I have millions or Billions of rows. What's the proper way to retrieve tag counts for a huuuge set of data. I don't neceserrily need to implement something, but I'd quite like to know how one would get around the performance aspect of such a huge table. Ideas are welcomed greatly..Thanks.
View 3 Replies View RelatedMy question takes two parts; firstly, is the new table that I'm proposing going to handle the business logic I describe below, and secondly, if I put the new table in, how in hell do I use it?
Right; present schema attached.
The idea, which I hope is fairly clear from the schema, is that you send it a buch of parameters about the event, admission date, etc, and it will return all tickets matching those parameters. An example stored proc for this is below:
CREATE PROCEDURE [dbo].[getSingleTicketsByParameters]
@eventIdINT,
@standIdINT,
@admissionDateIdINT,
@bookingDateIdINT,
@concessionIdINT,
@bookingMinQuantityIdINT,
@bookingMaxQuantityIdINT,
@membershipIdINT
AS
SET NOCOUNT ON
SELECT
[tblTickets].[id],
[tblTickets].[booking_date_id],
[tblTickets].[booking_min_quantity_id],
[tblTickets].[booking_max_quantity_id],
[tblTickets].[ticket_concession_id],
[tblTickets].[membership_id],
[tblTickets].[event_id],
[tblTickets].[stand_id],
[tblTickets].[admission_date_id],
[tblTickets].[price],
[tblTickets].[availability],
[tblTickets].[description],
[tblTickets].[admin_description],
[tblTickets].[ticket_open],
[tblTickets].[ticket_live],
[tblEvents].[event_name],
[tblEvents].[event_open],
[tblStands].[stand_name],
[tblStands].[stand_open],
[tblBookingDates].[booking_start_date],
[tblBookingDates].[booking_end_date],
[tblTicketConcessions].[concession_name],
[tblBookingMinQuantities].[booking_quantity],
[tblBookingMaxQuantities].[booking_quantity],
[tblAdmissionDates].[description],
[tblAdmissionDates].[admission_start_date],
[tblAdmissionDates].[admission_end_date],
[tblAdmissionDates].[date_open],
[tblMemberships].[membership_name]
FROM [tblTickets]
LEFT JOIN [tblEvents] ON [tblEvents].[id] = [tblTickets].[event_id]
LEFT JOIN [tblStands] ON [tblStands].[id] = [tblTickets].[stand_id]
LEFT JOIN [tblBookingDates] ON [tblBookingDates].[id] = [tblTickets].[booking_date_id]
LEFT JOIN [tblTicketConcessions] ON [tblTicketConcessions].[id] = [tblTickets].[ticket_concession_id]
LEFT JOIN [tblBookingQuantities] AS tblBookingMinQuantities ON [tblBookingMinQuantities].[id] = [tblTickets].[booking_min_quantity_id]
LEFT JOIN [tblBookingQuantities] AS tblBookingMaxQuantities ON [tblBookingMaxQuantities].[id] = [tblTickets].[booking_max_quantity_id]
LEFT JOIN [tblAdmissionDates] ON [tblAdmissionDates].[id] = [tblTickets].[admission_date_id]
LEFT JOIN [tblMemberships] ON [tblMemberships].[id] = [tblTickets].[membership_id]
WHERE 1=1
AND ([tblEvents].[id]=@eventId OR @eventId=0)
AND ([tblStands].[id]=@standId OR @standId=0)
AND ([tblTicketConcessions].[id]=@concessionId OR @concessionId=0)
AND ([tblAdmissionDates].[id]=@admissionDateId OR @admissionDateId=0)
AND ([tblBookingDates].[id]=@bookingDateId OR @bookingDateId=0)
AND ([tblBookingMinQuantities].[id]=@bookingMinQuantityId OR @bookingMinQuantityId=0)
AND ([tblBookingMaxQuantities].[id]=@bookingMaxQuantityId OR @bookingMaxQuantityId=0)
AND ([tblMemberships].[id]=@membershipId OR @membershipId=0)
GO
So. It's all about to get horribly, horribly complex (well, it is to me) so take a deep breath.
Tickets are subject to quotas. However, quotas are subject to... well, at the moment they can be based on event, stand, admission date, concession, or any combination of these. They can also be based on an individual ticket. All quotas, however, are annual; they only apply to ticket purchases in the same year. For example:
- you can't buy more than 4 tickets for date A, in stand B at event C, per year.
- you can't buy more than 2 tickets for stand D per year.
- you can't buy more than 4 of ticket number 123.
Now, I'm thinking that all I need to manage this is one table, and it's going to look a little like this:
tblQuotas
id INT PK
ticket_id INT FK
event_id INT FK
stand_id INT FK
admission_date_id INT FK
quota INT
So, if I put a record in there with an event, stand and admission date - and a quota - then I've met the first business rule that I described above. If I put in a record with just a stand id and a quota, then I've met the second sort. If I put in one with just a ticket id and a quota, then I've met the third.
Now we return to that big SQL statement above. The one that says "get me all eligible tickets that match these parameters". And it's got to get a lot bigger because I now need to not only join in tblQuotas, to see if any quotas apply to the ticket I've chosen (or to the event, stand, etc that make it up), but I've also got to join in tblBasket, and tblOrders, and tblUsers, to find out how much of any particular quota they've already used up in previous orders. Although that's only orders placed in the current year, mind. And of course I also now haveto pass the users ID in as a parameter so I can look up their order history.
Your head's hurting too, right?
So... this is where I've got to:
SELECT SUM(ticket_quantity) FROM [tblBasket]
INNER JOIN [tblTickets] ON [tblBasket].[ticket_id] = [tblTickets].[id]
INNER JOIN [tblOrders] ON [tblBasket].[order_id] = [tblOrders].[id]
WHERE
[tblTickets].[stand_id] = @standId
AND [tblOrders].[user_id] = @userId
AND ([tblOrders].[order_date] BETWEEN '2006/01/01' AND '2006/12/31')
What I want to do is incorporate that into the big SQL query up top, in such a way to make it only return tickets that not only match the ticket parameters but that also aren't linked to stands, admission dates or anything else, that the user has reached their quota on.
Oh, and I'm in a bit of a hurry so do try and get a move on won't you? ;)
But seriously - how do I put those two SQL querys together? Do I need one for each paramater that might have a quota attached, or is there a quicker way? All suggestions and advice, up to and including "get an easier job, dude", received gratefully :)
does email task only work with sql server deployment? I have an email task in my ssis package and i want go for file system deployment.
thanks,
kushpaw
I receive the following error when trying to invoke xp_sendmail 'xp_sendmail: failed with mail error 0x80070005'. However, for alert notifications the email works fine. SQL is using an alias account to send mail. This account doesn't have any funny characters. Any ideas why the alert system works but xp_sendmail does not? The invokation of xp_sendmail is being done by SA.
Thanks.
I've written several survey systems in which the majority of the questionshave the same or similar responses (Yes/No, True/False, scale of 1 - 5,etc).But this latest survey system I'm working on has 8-10 sections, with avariety of question attributes and answer scales. Some items have just adescription and require a Yes/No answer, others have a description and anactive status and require a Yes/No and price answer, some require a comment,etc.Rather than build a separate response table for each survey section, I wasthinking of building one generic response table, and trying to force allsections to fit by adding columns - some of which won't apply to some items.Like this:Survey Category (will apply to all items)Survey Section (will apply to all items)Item Description (will apply to all items)Item YN (will apply to all items)Item Price (will apply to about 10% of the items)Item Points (will apply to about 10% of the items)Item Active YN (will apply to about 10% of the items)Item Fail YN (will apply to about 10% of the items)Item Comment (will apply to about 10% of the items)For instance, in the structure above the field "Item YN" would representmultiple types of answers: is the item in use?, is the item in place?, isthe item given away for free?, is the item on display?, etc. Basically,anywhere a Yes/No answer is used.The advantage is one source table (rather than 8) for storing answers, andit might be easier to query and report on.The disadvantages I see are 1) it's more difficult to understand the meaningof the responses when the answer field is named Item YN, and 2) you have anon-normalized table that's difficult for a 3rd party to understand.If I have the questions and responses in separate tables, I'll use nameslike "ItemComplimentaryYN" and "ItemUsedYN" depending on the question. It'seasier for others to learn the data.I actually don't like the "generic" approach, and probably won't use it, butI figured I'd try to get some input from others who've written surveysystems.Thanks
View 5 Replies View RelatedHello ALL
what I want to achieve is to load a text file that has email addreses from disk and using the email addresses in the text file look it up against the email addresses in the database table then once matched delete all the users in the table whose email address were in the text file.
I also want to update some users using a different text file.
Please help me with the best way to do this
Thanks in advance
Hi,
I currently have an internal message system, and I want to modify the db design so users can create their own custom folders.
Currently I have just this table in use, with the bolded column, the one I want to add. With this design, I am thinking of defaulting each "folderID" in this table to a value of 0, which will denote the standard inbox folder. I think this is better because I don't think its necessary or beneficial to have each user have their own row in this table just for their standard inbox.
CREATE TABLE [dbo].[tblMessage](
[MessageID] [int] IDENTITY(1,1) NOT NULL,
[MessageFrom] [int] NOT NULL,
[MessageTo] [int] NOT NULL,
[Message] [varchar](1500) NULL,
[prevMessage] [varchar](500) NULL,
[Subject] [varchar](50) NULL,
[date] [smalldatetime] NULL,
[Checked] [tinyint] NULL,
[deletedbySender] [tinyint] NULL,
[deletedbyRecipient] [tinyint] NULL,
[IP] [varchar](15) NULL,
[folderID] [int] NULL
)
I am planning on adding a table like this below
CREATE TABLE [dbo].[tblMessage_folders]
(
[folderID] [int] IDENTITY(1,1) NOT NULL,
[userID] [int] NOT NULL,
[folderName] [varchar](50) NULL,
[dateCreated] [smalldatetime] NULL,
)
Any differing opinions, or anyone agreeing with me I would love to hear your opinions. I'm just want to be sure this doesnt create any problems I might not be seeing.
Thanks once again!!
mike123
I am preparing to design an application that will archive files created by another application. In my SQL database I want to store details about the file and then the file its self. Each file is about 500kb in size and there will be about 20,000 files generated per year.
My preference is to store these files in a blob field. It makes storage, linking to file meta data, backup etc easy for me. I have already solved the technical issues surrounding pulling the file in and out of a blob field.
By my calculations I will need a server with 10GB of disk space for each year of files archived which doesn't seem outlandish for table size.
I do not want to design my application however to find out a year from now that I should have been storing these files in a traditional file system because of (... whatever ...) and just linking them by path in the sql database.
I'm curious what users of this forum believe to be the best practices surrounding this type of database?
I have a scenario like below
Product1
Product2 Product3
Product4 Product5
Product1 1
1 0 0
1
Product2 1
1 0 0
1
Product3 0
0 1 1
0
Product4 0
0 1 1
0
Product5 1
1 0 0
1
How to design tables in SQL Server for the above.
I am doing some ssis package development in my local machine. I have a requirement to send email the output.
I am developing in my local machine. Is it possible to configure SMTP configuration in my local system to receive the email in Gmail?
if yes, what all are the steps to be followed.
Ok, I'm doing a football database for fixtures and stuff. The problem I am having is that in a fixture, there is both a home, and an away team. The tables as a result are something like this:
-------
Fixture
-------
fix_id
fix_date
fix_played
----
Team
----
tem_id
tem_name
-----------
TeamFixture
-----------
fix_id
tem_id
homeorawayteam
goals
It's not exactly like that, but you get the point. The question is, can I do a fixture query which results in one record per fixture, showing both teams details. The first in a hometeam field and the second in an away team field.
Fixture contains the details about the fixture like date and fixture id and has it been played
Team contains team info like team id, name, associated graphic
TeamFixture is the table which links the fixture to it's home and away team.
TeamFixture exists to prevent a many to many type relationship.
Make sense? Sorry if this turns out to be really easy, just can't get my head around it at the mo!
I actually work in an organisation and we have to find a solution about the data consistancy in the database. our partners use to send details to the organisation and inserted directly in the database, so we want to create a new database as a buffer database to insert informations from the partners then make an update to the main database. is there a better solution instead of that?
View 6 Replies View RelatedHello everyone,I have a webcontrol that uses database-structures alot, it uses the system tables in SQL to read column information from tables. To ease the load of the SQL server I have a property that stores this information in a cache and everything works fine.I am doing some research to find if there are anyway to get information from the SQL server that the structure from a table has changed.I want to know if a column or table has changed any values, like datatype, name, properties, etc.Any suggestions out there ?!
View 3 Replies View RelatedI have a system that basically stores a database within a database (I'msure lots have you have done this before in some form or another).At the end of the day, I'm storing the actual data generically in acolumn of type nvarchar(4000), but I want to add support for unlimitedtext. I want to do this in a smart fashion. Right now I am leaningtowards putting 2 nullable Value fields:ValueLong ntext nullableValueShort nvarchar(4000) nullableand dynamically storing the info in one or the other depending on thesize. ASP.NET does this exact very thing in it's Session State model;look at the ASPStateTempSessions table. This table has both aSessionItemShort of type varbinary (7000) and a SessionItemLong of typeImage.My question is, is it better to user varbinary (7000) and Image? I'mthinking maybe I should go down this path, simply because ASP.NET does,but I don't really know why. Does anyone know what would be the benifitof using varbinary and Image datatypes? If it's just to allow saving ofbinary data, then I don't really need that right now (and I don't thinkASP.NET does either). Are there any other reasons?thanks,dave
View 7 Replies View RelatedHi All,Can u please suggest me some books for relational database design ordatabase modelling(Knowledgeable yet simple) i.e. from which we couldlearn database relationships(one to many,many to oneetc.....),building ER diagrams,proper usage of ER diagrams in ourdatabase(Primary key foreign key relations),designing smallmodules,relating tables and everything that relates about databasedesign....Coz I think database design is the crucial part of databaseand we must know the design part very first before starting up withdatabases.....Thanks and very grateful to all of you....Vikas
View 3 Replies View Relatedin the database, there is some information about the applicant: his name, his email, his status (accepted or rejected)....etc.
the page that is shown to the administrator displays the applicant name and his status, when he changes any of the applicants status, an email is sent to that applicant.
is there any way to do that?
thanks in advance
Can I send an email that will update a database table? say someone replies 'yes' to the email, where would I start to place that yes answer into the database table?
Thank-you,
Eric
Hi everyone,
I'm having problems sending my query results as a csv file. Ideally I would be able to send my results from a sql job every night as a csv but I'm having problems doing it. My code is shown below
Code Block
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'admin'
,@recipients = m@m.com
,@subject = 'Notification Created Today'
,@attach_query_result_as_file = 1
,@body_format = 'text'
,@query ='
select
a1.c#, cli, fe, type, dateReq, p, (select convert(varchar(50),convert(money,amount),1)), (select convert(varchar(50),convert(money,vat),1)),(select convert(varchar(50),convert(money,total),1)), cReq
from Disb_1 a1 inner join Details on a1.c#=Details.c#
where datediff(day,getdate(),dateReq)=0 and dateReq <= getdate()'
I've tried many different things and been through the parameters of sp_send_dbmail but I just cant find anything.
Also, how can I use column names for those money conversions? At the moment it just says 'no column name'
Thanks,
Steve
I have a report that gets sends out through a subscription and sometimes the report has multiple pages and all those pages appear within one email.Is it possible to set the subscription in such a way that an email is sent per page when the subscription executes.
View 2 Replies View RelatedUnder IIS SMTP I can set bounced email redirect etc. how to do that with dbmail, the idea is I can get the list of bounced emails somewhere so I can create a report.
Any idea?
thanks
While attempting to set up sql replication in MSSQL 2005 one of my user databases is now in the systems database folder. I need to move it back to the user databases folder. Any help would be greatly appreciated.
View 16 Replies View RelatedHi all
I have heared that there is way to use SQL SMO (SQL Management Objects), or other third party tools running on a management server to send out an email or some kind of notifications if a database is locked.
Anyone knows how to do that in SQL Managment 2005.
Thanks
-Sarah
I have a website that I want to put a contact form on so I can allow users to send emails from it. I have SQL Server as my db but my hosting co. won't allow me to use the Database Mail procedure to send emails (msdb.dbo.sp_send_dbmail I think it's called).
I will have to write my own procedure to do it instead. Can anyone point me in the direction of some good resources where I might get some info on how to do this?
I am havig trouble getting database mail to work. I setup IIS on the same box as sql. I setup SMTP with the relay to 127.0.0.0. In sql server, I setup database mail to point to localhost, with no authentication. After the setup, when I test the email (RMB Send test message) it works fine.
I created a job that will fail every time. I setup myself as the notify party on the job. When I run the job, it fails. I get no email. The job log has the following error:
Message
The job failed. The Job was invoked by User xxxxAdministrator. The last step to run was step 1 (test 1). NOTE: Failed to notify 'Dan Jones' via email.
I have spent lots of hours trying to make this simple thing work. What is wrong?
need help
split list of email add comma for evry email
i have tabe "tblLogin" and in this table i have field emall
like this
emall
-----------------------------------------
aaa@hhhh.mm
nnn@hhhh.mm
mmm@hhhh.mm
need to do ilke this
Code Snippet
@list_email = (SELECT emall FROM tblLogin)
--------------------------i get this
-----------------------@list_email=aaa@hhhh.mm ; nnn@hhhh.mm ; mmm@hhhh.mm
@recipients = @list_email
Code Snippet
IF EXISTS( SELECT * FROM [db_all].[dbo].[taliB] )
BEGIN
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
Date_born AS 'td','' ,
Age AS 'td','' ,
BirthdayToday AS 'td','' ,
BirthdayThisWeek AS 'td'
FROM [Bakra_all].[dbo].[taliB] ORDER BY LastName FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><H1 align=center>aaaaaaaaaaaaaaaaaaaaaa</H1><body ><table border = 1 align=center dir=rtl>
<tr>
<td>name</td>
<td>fname</td>
<td>date</td>
<td>age</td>
<td>aaaaaaaaa</td>
<td>bbbbbbbbbbbbbbb</td>
</tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =N'rrr@iec.co.il',
@copy_recipients='rrrrr@iec.co.il',
@body = @body,
@body_format ='HTML',
@subject ='ggggggggggggggggggggg',
@profile_name ='ilan'
END
ELSE
print 'no email today'
Hi All,
I am designing database where few of the master tables will reside in different database or in case different server. Scenario is
Server "A" with Database "A" may host the "Accounts" table.
Server "B" with Database "B" may host the "Product" table.
I am designing database "Project" which will hosted in Server "A".
My application requires this master tables [readonly access] as data inserted in my application refers this tables. Also there are reports to be generated which refer this tables.
How do i design my database and sql queries?
I am thinking of approach of having equivalent tables created in my database and writing service which keep tables in my database in sync. This will ensure good perfomance during transaction and reports as they will need to refer this table locally as opposed to different database or different server.
Any thoughts on above approach?? or any better/standard way for such scenarios ?
Thanks in Advance. Your inputs will be of great help.
Hello:
My client has a db with the following structure:
Online US Searchable Map of the 50 US States. Users search criteria is the following: Query records by selecting state, county, then record. Each County table has 10-20 tables. All databases combined = 500MB and TLogs = 100MB.
How would you re-design a relational DB where users could query data by state-county-record. Currenty the DB's are created by the County of each state which creates hundreds of DB's in SQLServer with no realtionship to each US state. What would be the best design to ensure good performance, data integrity and maintenance? Would you create 1 DB with all 50 states, create 4 DB's and divide by region(N,S,E,W), 50 DB's of each state or leave it as is with each county it's on DB? Any suggestions would be appreciated.
thx
rob
We have a ASP.Net 2.0 web application and need to send out emails. We have an EmailQueue table in our database where email gets stored before it is send out. We are looking at the pros and cons of using Database Mail in SQL Server 2005 to send out our emails. Should we use SQL or our web app to send email???Tips, tricks, articles, experiences, opinions are greatly appreciated. Newbie
View 2 Replies View RelatedFrom SQL Management Studion I go to Management > Database Mail and I am trying to send a test email but I never receive anything. I checked my SMTP Mail Server Logs and I saw no entry of my test email.
I also I checked my SQL Database Mail Logs and everything seems fine, no errors are reported.
The msdb.dbo.sysmail_allitems shows my email status as "sent".
So what am I missing? What steps would you recommend for troubleshooting my problem?
Thank you,
Ric
i ve a filed named "Email" of datatype .i ve created this field to insert email address in this field.but when i insert an email address like "bilal@yahoo.com" from a textbox wh is in an ASP.net webpage then in the filed i see this "System.Web.UI.WebControls.TextBox".
can u plz tell me how to fix this
Regards
Ahmed Bilal Jan
i've got a brand new server and just installed SQL 2005.
when i try to send a test email, i get the following error message:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 5 (2006-11-23T11:49:34). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it). )
I have checked all items from troubleshoot and eveything is ok...any help ?
thanks
Trying to find tsql for TRIGGER which will send me email, if someone rename database in test/dev environment. I found it for CREATE and DROP database trigger but could not find for RENAME database.
View 8 Replies View Related