Database Idea

Good morning

In messages system i have table to store messages and another table to contain the links which the message is posted through

Now i want to delete a link but i don't want to delete the message sent through this link

The problem:

When displaying the message sent what will be fount in the link field (i.e for the deleted link)

I tried to move the deleted link data to separate table (EX: deletedLink) but if the user added new link with the same name as the deleted link?The problem mainly in when displaying the message sent i will have the same link twice one for the deleted and the other for the added one.

If any one has a good idea for doing that please reply to me

I don’t want the sql code

I want just the idea

Thanks

kind regards

Mohammed Al Maghraby

Hey,

I posted in your other forum post.

Database help

hii have a DB scenario, it may be basic to u, but i am new to this so plz explain in details or if you can provided links to related articles that will be great.

i have sql server 2000 and i am using dataset on my asp.net application. now as Dataset is connenctionless, what will happen if i have filled my dataset with some records from database and updated those records in dataset. in that time some other user deleted some of those records from database. now when i will try to batch update database using my dataset (with records that has been deleted from DB) what will happen? will i get an exception or what. plz explain in detail . any links to articles will be great.

my second question is same, what are the methods to lock database if one user is accessing (updating) one record, so that other user dont get to change it at the same time? do i have to manage it in Database or what.

thank you in advance

regards

Hi,

I think this article will explain a lot

http://www.asp.net/learn/data-access/tutorial-21-vb.aspx

|||

Hi zeeshannasir,

now as Dataset is connenctionless, what will happen if i have filled my dataset with some records from database and updated those records in dataset. in that time some other user deleted some of those records from database.

My question is: what do you mean by "dataset is connectionless"? Do you mean that your database has been detached in your database engine? Well,in that case your database won't be accessible at all so you(and all the other users) cannot modify your database.

As to your second quesion, actually you don't have to worrry about it.All the transactions in SQL has been designed to comply with theACID (Atomicity ,Consistency, Isolation Durability) compliance.For example, if user A is currently opening up and reading the info. of a particular database, and meanwhile, user B is trying to open up the same database. Since there is no modification being made to the database, user B is allowed to open up. However, if user A is writting new data to the database, all the operations user B makes will be denied.

Hope my suggestion helps

|||

hi Bo Chen

what i meant by "dataset is connectionless" is that, i filled my dataset with required data using a stored procedure. now i am not connected to database, plz correct me if i am wrong. any changes i made to dataset is local until i dont batch update manually, is it correct. now what will be the scenario if this is the case, i have filled my dataset and made some changes and while i was doing that someone else also access the same database and changed some of the records.

plz correct me if i am wrong

thanks

Database design question

I have a need to keep a bunch of data regarding some wan links, and I don't know how to design my database for it.
Here's the data I need to store.

I have about 35 different links, and about 50 different application volumes that need to be stored in this database, for every hour of the day.
Right now the way I have it setup doesn't seem too good, but i can't figure out any other way.
I currently have 1 different table for each link, and every application as a column in these tables.
So for example:
Table Link 1
Date App1 App2 App3 App4
Jan 1 01:00 54613 351546 6848435 6847684
Jan 1 02:00 668468 6846433 646464 6546846

Table Link 2
Date App1 App2 App3 App4
Jan1 01:00 6846 6844354 6846434 38463434
Jan1 02:00 648458 3848646 6846 684684

etc.

The primary key on each table is the date.
I'm only storing about 7 applications right now, but I need to be able to increase this to about 50, so it's gonna make for huge queries for reporting.
The thing is I need to be able to report on total application volumes across every link.
So that means right now i'm doing a Sum(Link1.App1) + Sum(Link2.App1).... which I have to do 35 times, and then repeat again for the next application. And most reports have a lot of applications required so it makes for queries that are 20 pages long...

Is there any design solution that would make this more efficient?
Thanks!

You need to supply more information. It sounds to me like you might need an APPLICATION table to describe each applaction and a LINK table to describe each specific link -- but without addtional information might change that. It also sounds like you need at least one more table to record your time-sensitive data; however, I don't clearly see this at the moment.

Like I said: More information is needed.

|||

Ok.

Basically, let's say I have 35 different wan links going across my network.

Going across those links are a bunch of different applications. Let's say there's 50 of them.

I have a system that records the volume generated by each application over everyone of those 35 links, every hour.

Now I need to be able to store this data long term.

So the only thing being stored really is the volume for each application.

Right now it's setup this way:

TableLink1

Date Citrix FTP Telnet HTTP ..................etc

Jan 1 01:00 839203 923849 239487 83823

The date/time is the primary key as there will never be two entries for the same date/time.

The numbers being stored under the application columns are bytes.

I will need to be able to report on a few different things. For example, I will have to be able to produce the Top 10 applications for a given time period.

So that will mean i'll have to add up every application for every link and determine which 10 volumes are highest. With 35 different tables right now, my query

is extremely long and I don't know if it's supposed to be like that.

There's also a requirement for reports on a specific application across all links. So for example, I'll need to show the total volume used by FTP across

the 35 links for a given time period.

With the design that i'm using right now, it's possible, but it's a long query which looks like:

SELECT SUM(TableLink1.Citrix) + SUM(TableLink2.Citrix) + SUM(TableLink3.Citrix) .....etc AS Citrix

I hope this is enough info...if not tell me what you require. I'm pretty sure my design is ok, but just want to confirm if there's no better way.

I was also considering a different design that would look like this:

ApplicationCitrix

Date Link1 Link2 Link3 ...... Link35

Jan 1 01:00 23487234 234234 23487 ...... 3248324

Basically instead of using a different table for each link, i'd use a different table for each application...but I think it comes back to the same thing.

|||

Probably create application table something like this:

tb_APPLICATION

ApplicationID tinyint identity(1,1)

ApplicationName nvarchar(255)

Link table something like this:

tb_LINK

LinkID tinyint identity(1,1)

LinkValue bigint

and finally a cross-reference table:

xRef_App_Link

ApplicationID (fk)

LinkID (fk)

LinkStartDate smalldatetime

LinkEndDate smalldatetime

|||

Umm...i'm not sure i'm quite following...

If you can explain a little more maybe.

I don't get how the tb_LINK table would work...

How can I store a LinkValue without associating it to a specific application...

The way i'm thinking is maybe this:

keep the same tb_APPLICATION

then the tb_LINK:

LinkID tinyint identity(1,)

LinkName nvarchar(255)

And then the cross reference table:

xRef_App_Link

ApplicationID (fk) (pk)

LinkID (fk) (pk)

Date (pk)

Volume

Would this work?

I didn't quite get the part where you wrote:

LinkStartDate smalldatetime

LinkEndDate smalldatetime

|||

I think you want the app table:

tb_APPLICATION

ApplicationID tinyint identity(1,1)

ApplicationName nvarchar(255)

I am not sure if you need a LINK table or whether all you need is a VOLUME table that contains something like

Volume

RowId identity

LinkId

ApplicationId

LinkVolume

LinkStartTime

LinkEndTime

|||

Ok,

I think I will do it this way instead of having 35 tables lol...

Thanks a lot for the help.

|||I did not mean my response as "the answer" and I think it is pre-mature to mark it as such. I was hoping to get other opinions. Other ideas?

database Design Idea

Good morning
In messages system i have table to store messages and another table to contain the links which the message is posted through
Now i want to delete a link but i don't want to delete the message sent through this link
The problem:
When displaying the message sent what will be found in the link field (i.e. for the deleted link)

If any one has a good idea for doing that please reply to me
I dont want the sql code
I want just the idea
Thanks

kind regards
mohammed Al MaghrabyIs this a one-to-one relationship? one message for one link? Or, could one link refer to many messages? Or vice-versa.|||it is one to many relationship
one link can be used for many massages|||one link can be used for many messages

therefore the message has a foreign key to the link

now you delete the link, and you want the message to remain

and you're asking what will be found in the link

the answer: whatever you defined in the foreign key's ON DELETE option

in sql server 2000, the ON DELETE options are CASCADE (which you don't want) and NO ACTION (which would prevent the delete), so in sql server 2000, you can't do what you want with a foreign key!!

in sql server 2005, the ON DELETE options are NO ACTION, CASCADE, SET NULL, and SET DEFAULT (more in line with the various options defined by the sql standard)

helps?|||First Thanks for interest

There is misunderstanding in the problem
I have to enable the user to add a link with the same name because the original link supposed to be deleted
I.e. it doesn't appear in the forms of the application
So the user thinks that the link was deleted
Again

i want to delete the link because it became useless for me but at the same time there are messages in the message table which are related to that link
And I don't want to delete them
Also at the same time after deleting the link the user wants to view a report about the messages which contain information about the link used for sending these messages
, after deleting the link where could I get the link information?...because the message link data is very importatnt to me to know ......... that is the problem

I hope the situation is clear

Again thanks for interest


Kind regards

Mohammed Al Maghraby|||It sounds like you might want a sort of archive or history table. Just have a "deleted_links" table. The messages can remain in the messages table, they will just relate to the link you move to the deleted_links table. Or you could add a "current" field to the links table. Instead of deleting the link, you would set current to false.
Is that what you mean?|||I have to enable the user to add a link with the same name because the original link supposed to be deleted

Is "LinkName" (or equivalent) your primary key?