First time programming - need help with database design... - Database Discussion Boards

guptalav wrote:
what is a Tenant Database?

The only thing I can think of is that it is a database that contains details of tenants (people that rent properties).

It might be useful if you provide the context in which you find this term.

ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?

I think they use the term Tenant Database when more than one organization share the same datasource.

I think it's relatively new term.

Strange tot hear that!

"Aim to go where U have never been B4 and Strive to achieve it"
http://groups.yahoo.com/subscribe/dotnetforfreshers
http://himabinduvejella.blogspot.com

hi i am facing one starnge problem...............
I have one table in SQL DB which has following constraints........

Indexes

IX_InvoiceTaxDetails_InvoiceDetailIndex clustered located on PRIMARY InvoiceDetailIndex
PK_InvoiceTaxDetails nonclustered, unique, primary key located on PRIMARY InvoiceTaxDetailsIndex

Constarints

FOREIGN KEY FK_InvoiceTaxDetails_InvoiceDetail No Action No Action Enabled Is_For_Replication InvoiceDetailIndex
REFERENCES BMJ.dbo.InvoiceDetail (InvoiceDetailIndex)
FOREIGN KEY FK_InvoiceTaxDetails_TaxCombinationsDetails No Action No Action Enabled Is_For_Replication TaxCombinationsDetailsIndex
REFERENCES BMJ.dbo.TaxCombinationsDetails (TaxCombinationsDetailsIndex)
PRIMARY KEY (non-clustered) PK_InvoiceDetailIndex (n/a) (n/a) (n/a) (n/a) InvoiceTaxDetailsIndex

Even though i have defined Primary key On column InvoiceTaxDetailsIndex my table has multiple data with same Value in InvoiceTaxDetailsIndex field

e.g

InvoiceTaxDetailsIndex InvoiceDetailIndex TaxCombinationsDetailsIndex TaxAmount TaxValueSlabIndex FoxUpdate UpdateDateTime
---------------------- ------------------ --------------------------- ----------------------------------------------------- ----------------- ------------------------------------------------------ ------------------------------------------------------
305123 143171 2803 778.57000000000005 561 NULL 2005-11-19 19:55:34.763
305123 143171 2803 778.57000000000005 561 NULL 2005-11-19 19:55:34.763

can any one tell me what could be the reason..........

Regards,
Ritesh

Ok, I have an assignment in front of my that demands that I build a sql statement on the fly and then populate a listbox. No problem , except only retrieve say first 100 rows. Then as the user hits the right scroll retrieve 100 more at a time till such time no more rows are left.

Any examples out there or suggestions.

Thanks Sniff | :^)

SELECT TOP 100 FROM tbl WHERE sortfield > lastid ORDER BY sortfield

----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin

Folks,

I'm trying to get data back from my IIS/SQL Server without having to refresh the client's page. I also have to do some calculations on the returned data, which is to be displayed in a table. I'm using Microsoft.XMLHTTP to communicate between the client and server pages as shown below:

<br />
On the client side:<br />
function GetAssyRecs(TheOperator)<br />
{<br />
    var szRequest = "<GetAssyRecs>";<br />
    szRequest += "<TheOperator>" + TheOperator + "</TheOperator>";<br />
    szRequest += "</GetAssyRecs>";<br />
<br />
    var objHTTP = new ActiveXObject("Microsoft.XMLHTTP");<br />
    var szURL = "GetAssyRecs.asp";<br />
    var szHttpMethod = "POST";<br />
    objHTTP.Open(szHttpMethod, szURL, false);<br />
    objHTTP.SetRequestHeader("Content-Type", "application/x-www-form-urlencoded");<br />
    objHTTP.Send(szRequest);<br />
<br />
    var szReply = objHTTP.ResponseText;<br />
    alert("status: " + objHTTP.status + " Reply: " + szReply);<br />
 }<br />
<br />
On the server side (GetAssyRecs.asp):<br />
<%@ Language=JScript %><br />
<% Response.Expires=0; %><br />
<br />
<%<br />
var adOpenKeyset = 1;<br />
var adPersistXML = 1;<br />
<br />
var objXmlDOM = Server.CreateObject("Microsoft.XMLDOM");<br />
var szXmlRequest = Request.Form;<br />
if (!objXmlDOM.loadXML(szXmlRequest))<br />
{<br />
   var sErrMsg = "Invalid XML Request String: " + szXmlRequest;<br />
   Response.Write(sErrMsg);<br />
   return;<br />
}<br />
<br />
var TheUser = objXmlDOM.selectSingleNode("//TheOperator");<br />
if ((TheUser == null) || (TheUser.text == ""))<br />
{<br />
var sErrMsg = "No TheOperator specified: " + szXmlRequest;<br />
Response.Write(sErrMsg);<br />
return;<br />
}<br />
	<br />
var sql="Select idnum, serial_number, rework_done, thedatetime from IDC_OperatorCounts where operator = '" + TheUser.text + "' and thedatetime > dateadd(hh, -13, getdate()) order by thedatetime asc;";<br />
<br />
var conn=Server.CreateObject("adodb.connection");<br />
conn.ConnectionString="driver={SQL Server};server=xidc-mrtline;database=IDC_Xerox;Trusted_Connection=yes";<br />
conn.Open;<br />
var rs=Server.CreateObject("adodb.recordset");<br />
rs.Open (sql,conn,adOpenKeyset);<br />
rs.Save (Response, adPersistXML);<br />
	<br />
rs.close;<br />
rs=null;<br />
	<br />
conn.Close;<br />
conn=null;<br />
<br />
objXmlDOM = null;<br />
}<br />
%><br />

What I want to do is to cast the returned xml recordset back into an ActiveXObject ("AdoDB.Recordset"), so I can read through the records using the syntax a recordset object offers.

Is this possible?

'til next we type...
HAVE FUN!! -- Jesse

I have one database that consist of few tables. My scenario is like this.

Goods import from the port can either be delivered to warehouse for storage or delivered direct to customers.

Goods delivered to warehouse for storage, will then be delivered to customers upon orders.

So, basically there's:
1. incoming from the port to warehouse
2. incoming from the port to customer
3. outgoing from the warehouse to customer
* All transports of goods is delivered by trucks

I'm planning to control all this using "Delivery Order Number" which is use for both inbound and outbound of goods. Is this even correct? Please help! Urgent.

Warehouse Table [only used when storage]
- Warehouse ID
- Warehouse Name
- Address
- Postcode
- City
- State
- Office Phone
- Office Fax

Customer Table [only used when product is directly delivered from port]
- Customer ID
- Customer Name
- Address
- Postcode
- City
- State
- Office Phone
- Office Fax

Port Table
- Port ID
- Port Name

Vessel Table
- Vessel ID (Not the true Vessel Number plate but just Identity Field in SQL DB)
- Vessel Name

Truck Table
- Truck ID
- Truck Number Plate
*(2 primary keys?)

Product Table
- Product ID (Not the real product ID but just Identity Field in SQL DB)
- Delivery Order No
- Vessel Name (or use Vessel ID better?)
- Product Stock Code Number (isnt this the same as product ID)
- Product Size
- Product Quantity (is this necessary?)
- Product Gross Weight (used when incoming)
- Product Net Weight (used only when outgoing)
- Product Case Number (used only when outgoing)
*(Actually, it's only ONE TYPE of product but it comes in different sizes/weights)

Inbound Delivery Table
- Order ID (Not the real Order ID by Customer Invoice but just Identity Field in SQL DB)
- Delivery Order Number
- Delivery Date
- Inbound From [dropdownlist of port names]
- Delivery To [radiobutton to choose whether direct to customer or to warehouse for storage. Radiobutton links to dropdownlist of customers/warehouses]
- Vessel ID (Or should I put Vessel Name instead?)
- Truck ID (Or should I put Truck Number Plate instead?)

Outbound Delivery Table
- Order ID (Not the real Order ID by Customer Invoice but just Identity Field in SQL DB)
- Delivery Order Number
- Delivery Date
- Inbound From [dropdownlist of port names]
- Delivery To [radiobutton to choose whether direct to customer or to warehouse for storage. Radiobutton links to dropdownlist of customers/warehouses]
- Vessel ID (Or should I put Vessel Name instead?)
- Truck ID (Or should I put Truck Number Plate instead?)

Hello every body
i need to get record only from one row from this view i mean this row shout show only book who are taken.The problem in this code is becasue is show me all users who take the book i need only last one.Pleas help me where to fix the code?
use library
select [title], [author], [first_name], [last_name],max([borrow_date]), [telephone], max([id_borrow])
from
[Return View]
where status=1
group by title,author,first_name,last_name,telephone
Good bye
Best regards

Question is not very clear.

select *
from [Return View]
where id_borrow = (select max(id_borrow)
from [Return View]
where cond....
and group by ..
)

Hope it works !!

Thanks
Mad Max

I would like to know what people do with phone numbers when storing them for an app. I have several ideas and I have implemented most of them depending on the app, but I want to get to a standard so I'm asking the question:
Where do you store phone numbers for your app?

Assuming I have a Clients table and Contacts table I have done the following in the past:
1. Created fields in both tables for each row such as Phone1, Phone2...
2. Created a seperate table phone numbers and created a reference table to hold the ids of the clinet/contact and the phone number.
3. Created a text field in both tables and stored the phone numbers as and XML string

I know that creating other tables and ref tables is probably the best Normalized way to do this but is it really that much of a memory/time savings over just storing the information in the same table as the Clients/Contact?

Any thoughts would be appreciated,
Richard

It really depends on what you are going to do with the information. If all that will ever happen is that the phone number gets displayed on the screen for a customer services person to see and then dial the number then having phone1, phone2 etc. is probably fine.

If you are storing more than just the phone number you might consider normalising it. e.g. for each phone number you are going to put what type of number it is (work, home, mobile, fax, etc.) and maybe a description, e.g. "Available between 9:00 and 18:00"

Alternatively, if you are going to do any serious processing with phone numbers (and I really can't imagine what that could be outside of a telco or automated call centre scenario) then you would be better normalising the data otherwise the SQL will get horrendous.

ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?

How do I sort a Dataset by a certain column value? The data will be bound to a datalist. The only sorting I've found is for the datagrid web control. Frown | :(

Hi.. what is the command to list all the tables of a data base in SQL SERVER???? Confused | :confused:

Please help me out in this situation????

Bawa

Bawasoft wrote:
what is the command to list all the tables of a data base in SQL SERVER????

SELECT * FROM INFORMATION_SCHEMA.TABLES

ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?

Thanks dear...

Bawa

Bawasoft wrote:
dear

Sorry, I realise English isn't your first language, but you should know that generally a person would only call their wife/husband "dear"

ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?

Thanks for the precision, honey Wink | ;)

--------

"I say no to drugs, but they don't listen."
- Marilyn Manson

Select name from sysobjects where xtype='u'
SELECT table_name FROM INFORMATION_SCHEMA.TABLES

These two performs one nad the same
But when executed in Query Execution Plan the first query costs less
So I prefer the first one

"Aim to go where U have never been B4 and Strive to achieve it"
http://groups.yahoo.com/subscribe/dotnetforfreshers
http://himabinduvejella.blogspot.com

Select name from sysobjects where xtype='u' order by name

"Aim to go where U have never been B4 and Strive to achieve it"
http://groups.yahoo.com/subscribe/dotnetforfreshers
http://himabinduvejella.blogspot.com

Sp_help 'databasename' gives all the tables (both System and user tables)of Database

"Aim to go where U have never been B4 and Strive to achieve it"
http://groups.yahoo.com/subscribe/dotnetforfreshers
http://himabinduvejella.blogspot.com

I am writing a program that updates a access database. I am filling a dataset with data from a comma delimited text file. Then I insert the dataset into the database with this piece of code.

OleDbAdapter.Update(dataSet1, "Table1");

This works great, but I also need to insert records to the database using a SQL insert statement. I am using the code below to insert a single record to the database.

"insert into Table1(col1, col2, col3)"
   + " values ('" + textBox1.Text + "', '"
   + textBox2.Text + "', '"
   + textBox3.Text + "')";

This works great. The problem comes in when I try to insert another dataset. The correct number of records are added but every row is the same as the row from the SQL statement. I bound the dataset to a datagrid but the datagrid shows the correct data in each row. Please help me before I get fired. Thanks

Hello Everyone, not sure if this is the correct forum for my question but im sure someone will tell me if it isnt, here goes with a little intro at my place of work we use a vb based app built on both sql 6.5 and 2k. i am currently a lowly helpdesk jockey but want more ( a la matrix !) im quite happy to dive straight in the deep end so i have this question which will i believe set me on the road to all things great, we use reusable scripts from within sqlew but i want to have a .exe from my desktop that i double click, select my script from a listbox enter the required values and then execute the script ( this script would have to execute across several networked workstations) i am familiar enough with terminology of both sql and vb i would greatly appreciate somebodies words of wisdom can this be done ?

regards

MM

I want to create a table from existing tables in SQL sever.

I have tried CREATE TABLE TBL_NAME AS SELECT * FROM EXISTNG_TBL_NAME. Confused | :confused:

But this command is not working in SQL Server. Confused | :confused:

Please Help me out in this situation.

Bawa

Hi there im new these forums but couldnt help notice your question, if i read your post correctly then cant you just dump or bulk copy out your table drop table then create new bulk copy in ? sorry if that doesnt help

MM