Database

12-Mar-09 3:50

You can shrink the logfile on demand. There's help on how to do that from the Management Studio here[^], or if you want to do this from SQL, look here[^].

That will only help until the file fills up again, or you'll have to hunt down the reason why the logfile is filling up. The documentation on the transaction log can be found here[^].

Good luck Smile | :)

I are troll Smile | :)

Multiple Databases

Brendan Vogt11-Mar-09 20:38

11-Mar-09 20:38

Hi,

I have a couple of databases, and I am trying to separate the users tables from all these databases, and put them into one table. So this means I will have to create a separate database that handles my memberships.

How does it affect performance if i work across databases? And how is it possible to have a foreign key link up to a primary key if both these tables are in separate tables?

How does a website like Amazon have multiple websites, and have products display over multiple websites? Surely they don't capture the same product for each instance of Amazon?

Regards

Eddy Vluggen12-Mar-09 4:08

12-Mar-09 4:08

Logging in to two servers will take more time than logging in to a single server. You can create a linked server in SQL Server, and get it's contents with a "SELECT * from [server][schema][table]" etc., but that will be a bit slower than a local table.

I guess that they replicate some tables across multiple databases (assuming that they're even using some sort of SQL)

HTH,

I are troll Smile | :)

Brendan Vogt14-Mar-09 20:32

14-Mar-09 20:32

Thanks, but please see below my reply Smile | :)

David Mujica12-Mar-09 4:40

David Mujica

12-Mar-09 4:40

You may want to re-think how your application works to simplify the data storage.

If I understand you correctly, do you have the following situation ?

Given multiple companies A,B,C,etc each having employees Tom, Dick and Harry, etc.

Within one table you want to store all employees for all of your companies.

One way to make a primary key would be to use a composite key of CompanyID + EmpID.

If you extend this type of logic across all of your tables, you can have an application that supports many companies all within the same database schema.

This is one way a Software-As-A-Service provider handles this situation. There are many complications to this model, but it could work for you.

Pros:
1) Easy backup. One database, everything is done in one shot.
2) Everyone is using the same schema design level; one application version to support.

Cons:
1) Nearly impossible to provide a rollback for a given company.
2) An applicaiton upgrade will force all of your companies to upgrade.

You can think of a whole pile of other pros and cons.

Hope this gives you something to think about. Smile | :)

Maybe I just ranted on something that has nothing to do with your question. D'Oh! | :doh:

Brendan Vogt14-Mar-09 20:31

14-Mar-09 20:31

Hi,

This is what I am trying to do. I have 2 websites running, each with it's database. One is a property website, the other a shopping website. I want to take all the users of both websites and put them into a centralised users table. The reason for this is to have 1 username and password that can be used for both websites. So my logic says to use a separate database that handles just user membership?

I can probably have all the tables (from both the property and shopping websites) in one database, but what happens if many users use the property and shopping website at the same time? Then SQL Server will go into overdrive and get slow? Or should I just get a faster server for this?

How else can this then be done?

Brendan

modified on Sunday, March 15, 2009 3:59 AM

Eddy Vluggen15-Mar-09 0:40

15-Mar-09 0:40

You could create a shared login-page as a separate website, and redirect them to the appropriate website once authenticated.

What's the relation between the two projects? Why does your logic want a separate database for membership, does it feel like it's easier to maintain?

If the projects aren't intertwined, then by all means, keep 'em separate Smile | :)

I are troll Smile | :)

Brendan Vogt15-Mar-09 5:25

15-Mar-09 5:25

Hey,

I have a domain like:

http://shopping.mydomain.com and
http://property.mydomain.com

So I am looking for a solution that has 1 login table that is used for both websites, I could put both websites tables into 1 database, what happens if the traffic to the sites increases? Then database activity increases which slow everything up. That is why I want to have a separate database for each website. And I want users to be able to login once, and access both websites.

Brendan

Eddy Vluggen15-Mar-09 5:47

15-Mar-09 5:47

Like David[^] suggested, I'd go for replication Smile | :)

I are troll Smile | :)

Replicate

David Mujica15-Mar-09 5:26

David Mujica

15-Mar-09 5:26

Ah, I now understand your issue.

Here is one last suggestion: Replicate your user/authentication tables.

The idea behind this is that it won't cause any change to your existing application logic and if the time comes where you want to sell one of your websites, you could just stop the replication. I'm not too afraid of performance problems because I doubt there is much write activity to these authentication tables and read access won't impact the replication process. The other upside to this is that you can bring a website down for maintenance without impacting the other website.

My 2 cents. Cool | :cool:

Re: Replicate

Brendan Vogt15-Mar-09 5:54

select upcoming birthday?

15-Mar-09 5:54

Thanks guys!!

So how will my membership tables look like, will I have one in each database?

Meax11-Mar-09 7:13

11-Mar-09 7:13

i have users Date Of Birth as DOB in sql server table called UsersProfile.

Person DOB
a 3/13/2009
b 3/13/2009
c 3/25/2009
d 4/2/2009
e 4/15/2009

How to select next(upcoming) birthday from this table?

if two persons bday falls on a same date, like Person a and Person b, how to select those two person?

Syed Mehroz Alam11-Mar-09 7:39

Syed Mehroz Alam

11-Mar-09 7:39

On top of my head, I can think of something like:

Select min(DOB) from UsersProfile where DOB > GetDate()

for getting persons, we can have:

Select Person from UsersProfile where DOB = 
     ( Select min(DOB) from UsersProfile where DOB > GetDate() )

Hope that helps.

Regards,

My Blog
My Articles

Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein

Meax11-Mar-09 8:06

11-Mar-09 8:06

thnaks for replying, however i am not getting any data with those quaries

Syed Mehroz Alam11-Mar-09 19:39

Syed Mehroz Alam

11-Mar-09 19:39

Strange.. It works for me. Here are my test statements:

declare @UsersProfile table
(
  Person varchar(20),
  DOB datetime
)

insert into @UsersProfile
  select 'Person1', '2009-03-11'
  union select 'Person2', '2009-03-13'
  union select 'Person3', '2009-03-13'  
  union select 'Person4', '2009-03-16'
  union select 'Person5', '2009-03-25'

select * from @UsersProfile 
  where DOB = ( select min(DOB) from @UsersProfile where DOB>GetDate() )

Are you sure your DOB column is of DateTime type?

Regards,

My Blog
My Articles

Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein

Luc Pattyn11-Mar-09 8:35

Luc Pattyn

11-Mar-09 8:35

Meax wrote:
i have users Date Of Birth

what is it these unborn people are using? D'Oh! | :doh:

Luc Pattyn [Forum Guidelines] [My Articles]

- before you ask a question here, search CodeProject, then Google
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get
- use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets

Meax11-Mar-09 8:38

11-Mar-09 8:38

sorry, meant to write 1980

Luc Pattyn11-Mar-09 8:54

Luc Pattyn

11-Mar-09 8:54

<br />
SELECT TOP 1 * FROM UsersProfile WHERE datepart(dy,DOB)>=datepart(dy,now()) ORDER BY datepart(dy,DOB)

is what I would try to get the next one.

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

Meax11-Mar-09 9:41

11-Mar-09 9:41

what if 2 people has same birthday?

like,

Person DOB
a 3/13/1980
b 3/13/1980

how to do it without writing TOP 2

Luc Pattyn11-Mar-09 10:10

Luc Pattyn

11-Mar-09 10:10

Hi,

Since the first reply suggested something like

Select Person from UsersProfile where DOB = <br />
     ( Select min(DOB) from UsersProfile where DOB > GetDate() )

it must be rather easy to get it like so:

Select Person from UsersProfile where DOB =<br />
     ( whatever it takes to obtain the next birthday )

So I would try:

Select Person from UsersProfile where DOB =<br />
( SELECT min(datepart(dy,DOB)) FROM UsersProfile WHERE datepart(dy,DOB)>=datepart(dy, now()))

BTW: This is PHP/MySQL code that works for me:

$Query="SELECT * FROM $table WHERE DAYOFYEAR(birthday)= ".
    "(SELECT min(DAYOFYEAR(birthday)) FROM $table ".
    "WHERE DAYOFYEAR(birthday)>=DAYOFYEAR(now()))";

Luc Pattyn [Forum Guidelines] [My Articles]

Meax11-Mar-09 21:10

11-Mar-09 21:10

How to make this work in sql server

<br />
"SELECT * FROM $table WHERE DAYOFYEAR(birthday)= ".<br />
		"(SELECT min(DAYOFYEAR(birthday)) FROM $table ".<br />
		"WHERE DAYOFYEAR(birthday)>=DAYOFYEAR(now()))";<br />