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 for experts

Hi,

I have 2 design related questions.

Q1: We are developing a huge .NET e-commerce web application with a number of modules - Shopping, 'For Sell' , 'For Rent', News, Jobs, Community, Matchmaking etc. These modules will store data into SQL server 2000 database server. 'For Sell' module will be used for all user ADs for selling items(add\update\search), similarly 'For Rent' module will be for Rentals ADs. The site will be open for 20+ different countries initially and will store unlimited ADs (eg. 200,000 For Sell ADs), Shopping Catalog (100,000 items).

We have some tables shared by all modules: module, module_category, module_subcategory, country, users, user_group etc. Some tables are module specific: forsell, forsell_attributes, forsell_att_values, shopping, shopping_review etc. The big design question that our team is facing is whether to make one single huge database and create all associated tables for all modules in it VS create separate database for each modules and have a central database for common tables.

Q2: Will it be better to create a single web application or different web application for each module?

Please give us your expert input\suggestions\tips that will guide our team to the right direction.

Thanks

Jennifer

The size of the database you're describing is nothing out of theordinary for an SQL Server instalation. More important than the numberof records is the number and type of queries you expect to be runningper second. If a single beefy (multi CPU Xeon with as much memory asthe system will take, fast IO subsystem) SQL Server can not handle theload then you could add more servers and build a cluster. I'm no expertat these matter and frankly if you need to come to this forum to learnabout these things the project may be a bit outside of your team'scapabilities. Assuming this is a professional, commercial applicationyou're probably better of hiring an SQL Server expert as a consultantfor a few weeks. But his or her effectiveness will be limited by howwell you know what you're trying to achieve.
In the meantime I recommend you do some experiments. Build a databaseserver and populate it with tables and arbitrary data. Then write someof the more complex and frequently called queries you expect to run.See how many of those you can run in a minute. That will give you someidea what performance limitations you're looking at.
One ASP.NET app that I wrote mines a database to generate a test resultreport. Depending on parameters it can run about 2000 queries, half ofthose are joins over 5 tables or more. The report completes in a minuteor two. This includes generating all the ASP.NET webcontrols etc. Themachine is a dual Xeon 2.8G with 2 GB RAM with mirrored SCSI drives.IIS and SQL reside on the same box.
I would not recommend splitting the application into multiple parts Idon't think there's a performance impact either way but you benefitfrom easier management in a single app. I'd be interested to hear otheropinions on this though.

Good luck!
|||

Nocturnal,

Thanks for your detailed post. Our 6 modules will have around 1 MILLION records in the database! even though we will be deleting old records regularly. Do you still call this an ordinary database for an ecommerce application? Here is more on the database internal: we create views by running complex join on the database involving ~10 tables. Each module has one big view against which we run the search queries for each module.

Having this said, do you still think ONE database with ONE web application suits our needs best?

What I am seeing from your comments is that making one WEB application will be easier to manage and performance impact won't hurt comparing to individual app for each module.

Thanks for your expertise valuable suggestions.

|||Hi,
first of all, I'm hardly an expert so take my suggestions with a grain of salt ok? :-)
I didn't mean to say that 1M records is normal for a e-commerceapplication but it's nothing out of the ordinary for SQL Server. SQLServer doesn't care what application stores its data there, after all.1 M e-ecommerce records is the same as 1 M cellphone records, inconcept.
1M records sounds like a big deal but it depends on the average recordsize as well. Imagine a 200 byte record. Loading the entire table inmemory will take only 200 MB plus overhead. Let's be conservative andcall the overhead 2x. That's 400 MB. Hardly worth mentioning on aserver that has 4 GB of RAM or more. Add some smart indexing and thistable can be searched very quickly.
There are volumes and volumes written about database optimization. Iknow very little about the subject and it's beyond what a forum canreally teach anyway. You should be able to learn the essence from acombination of studying the right books, experimentation and plain ol'experience.
It is good that you have some grasp of the scalability requirements ofyour application. Many designers don't. Now take it one step furtherand start experimenting with your database structures. Create somerepresentative tables and fill them with 1M random records. Then runthe queries against it that you expect your web app to run. Measureperformance. Measure server impact (watch for high CPU utilization andswapping). Modify the experiment, draw conclusions, refine your design.There's really no substitute for experimentation. Have a go at it andlet us know!
|||

jennifer7290 wrote:

We have some tables shared by all modules: module, module_category, module_subcategory, country, users, user_group etc. Some tables are module specific: forsell, forsell_attributes, forsell_att_values, shopping, shopping_review etc.

You should probably start by having proper names for your tables. A table represents a SET of entities. "Modules", not "module". Also, the name "forsell" and "shopping" makes very little sense.What is a "shopping"? Use names that describe what the table contains. See ISO-11179.

jennifer7290 wrote:

The big design question that our team is facing is whether to make one single huge database and create all associated tables for all modules in it VS create separate database for each modules and have a central database for common tables.

Q2: Will it be better to create a single web application or different web application for each module?

The single largest problem you'll face with many applications is the inability to share session state -- session state goes away at the application boundry, so you'll have to develop your own site-wide state management - which may not be so bad, anyway, as a lot of applications I see go with shared Context model.

As far as your database, it doesn't have to reflect your application. A database can have quite literally a crap load of tables (2B is the max I believe) and you'll be more than fine with a few hundred. Separate databases mean separate Roles, Users, etc -- and can be quite a pain to set up. I'd recommend sticking with one -- unlike the application, you have a common "security" module shared by each database.

|||

Hello, good question... a single database or multiple databases?

Let's consider

1) A table with 10 Milion of rows is not a problem for SQL server if we use a proper index

2) 10 Tables of 1 M of rows are better then a table of 10M of rows

3) Performace ofter depends on applications that use a database (use pooling)

4) Make minimal queries and use recorset in memory (1 query of 1000 rows is batter than 10 queries of 100 rows)

I suggest 1 DB.

Hello!

|||Thanks to Alex and Claudio for sharing your valuable input. That really matters. Have a nice day!

Database Design Question

Hi Guys

I have one parent entity that is related to two other entity with one to many relatetion ship (i,e main entitys primary is becoming part of primary key of other entitys i,e Identifying relation ship).when these
two entitys are involved in many to many.(one column (primary key of main entity) is common in both the entitys).then what would be primary key of intersetion table or associative table.

ex : A(projId) --> B(ProjId,P1)
-->C(ProjId,P2)

When B and C are involved in manay to many realteionship
ProjId is common in both the entitys.

(... ) is primary key

Can any body explain what would be the scenario.

Thanks
UmashankarI may be mis-interpreting your question, but I think the answer is that there is no relationship between Tables B and C outside of the relationship that exists through Table A. I guess you could say that there would be a Many-to-Many relationship between B and C, but I don't think that it would be very useful to any kind of informative analysis (short of alway using SELECT DISTINCT on the tables). Any kind of JOINs that you are going to do between B and C are always going to have to include A.

Table A
ProjID int IDENTITY,
ProjName

Table B
ProjID int,
TaskID int IDENTITY,
TaskName

Table C
ProjID int,
CostID int IDENTITY,
CostName

If this doesn't answer your question, perhaps you could rephrase it or include more detail in your DDL post.

Regards,

hmscott|||Thanks hmscott.

I am trying to understand the existing database datamodel.

In this Project ,Task,Assignment,Resource are four entitiys.

Primary Key Information:

Project :Proj_Id
Task :Proj_id,Task_uid
Resource :Res_uid,Proj_id
Assignment :Assn_uid,Proj_id

In a given model thare is
1.one to many relationship(identifying) between Project and Task.
2.one to many relationship(non Identifying) between Task and Assignments.
3. one to many relationship(non Identifying) between Resource and Assignments.

Surprising to me thare is no relationship shown between project and Assignments in datamodel diagram.But Assignment entity has Proj_id as part of primary key.

Is Proj_id in Assignments entity came due to relation with tasks and resource entity.

Regards
Umashankar




Originally posted by hmscott
I may be mis-interpreting your question, but I think the answer is that there is no relationship between Tables B and C outside of the relationship that exists through Table A. I guess you could say that there would be a Many-to-Many relationship between B and C, but I don't think that it would be very useful to any kind of informative analysis (short of alway using SELECT DISTINCT on the tables). Any kind of JOINs that you are going to do between B and C are always going to have to include A.

Table A
ProjID int IDENTITY,
ProjName

Table B
ProjID int,
TaskID int IDENTITY,
TaskName

Table C
ProjID int,
CostID int IDENTITY,
CostName

If this doesn't answer your question, perhaps you could rephrase it or include more detail in your DDL post.

Regards,

hmscott