question on table structure

All,

On my aspx page i have 2 sets of checkboxlists, each with a number of items and i am storing that info in my db. currently i have my table set up this way:

InfoID--PK auto

UserID-int

CheckBoxList1ID-int

CheckBoxList2ID-int

So a user can select more than 1 item in each checkbox, so this table will contain multiple user-ids and each id from the respective checkboxes. My question is, is there a better way to store this data? I hope this is clear, any input would be greatly appreciated.

Thank you.

I would have a table that holds the values for the checkboxes, and another table that bridges the user table and the checkbox value table. This would only have two fields - the user ID and the selected checkbox ID. This is a better way to manage many-to-many relationships. You would have to iterate through the checkboxes and insert a row for each one the user selected.http://www.mikesdotnetting.com/Article.aspx?ArticleID=53

|||

Thanks mike..actually I do have those other 2 tables as well, and I am pretty much doing it like that link you sent, just didn't know if there was a better way. Since thats the way to go, i am happy i don't have to recode.. . thanks for the response.

question on SQL schema

My company is currently developing a new database driven
application. Originally we had designed the structure
with three database's (based on update schedule and
administration). My Oracle DBA has looked over the plan
and recomends we put the three databases into one and
seperate as multiple schemas. I have looked all over
microsoft.com and haven't found anything that discusses
multiple schemas. I'm guessing its a different
terminology on the Microsoft side.
How would I go about developing a single database that is
segregated into three so called schemas. I know Oracle
uses [schema.]tablename and SQL uses
server.database.owner.tablename. Does this mean I create
an owner for each dataset (schema).
thanks
JustinHi Justin,
I think you are right. Oracle uses [schema.]tablename and SQL uses
server.database.owner.tablename. You can create an owner for each dataset
(schema).
You may want to know what is "owner" in SQL Server, please refer to "Owners
and Permissions", "Database Object Owner" in SQL Books Online.
~~~~~~~~~~~~~~~~~
Every object in Microsoft SQL Server 2000 is owned by a user. The owner is
identified by a database user identifier (ID). When an object is first
created, the only user ID that can access the object is the user ID of the
owner or creator. For any other user to access the object, the owner must
grant permissions to that user. If the owner wants only specific users to
access the object, the owner can grant permissions to those specific users.
For tables and views, the owner can grant INSERT, UPDATE, DELETE, SELECT,
and REFERENCES permissions, or ALL permissions. A user must have INSERT,
UPDATE, DELETE, or SELECT permissions on a table before they can specify it
in INSERT, UPDATE, DELETE, or SELECT statements. The REFERENCES permission
lets the owner of another table use columns in your table as the target of
a REFERENCES FOREIGN KEY constraint from their table. The following example
illustrates granting SELECT permissions to a group named Teachers and
REFERENCES permissions to another development user:
GRANT SELECT ON MyTable TO Teachers
GRANT REFERENCES (PrimaryKeyCol) ON MyTable to DevUser1
The owner of a stored procedure can grant EXECUTE permissions for the
stored procedure. If the owner of a base table wants to prevent users from
accessing the table directly, they can grant permissions on views or stored
procedures referencing the table, but not grant any permissions on the
table itself. This is the foundation of the SQL Server mechanisms to ensure
that users do not see data they are not authorized to access.
Users can also be granted statement permissions. Some statements, such as
CREATE TABLE and CREATE VIEW, can only be executed by certain users (in
this case, the dbo user). If the dbo wants another user to be able to
create tables or views, they must grant the permission to execute these
statements to that user.
~~~~~~~~~~~~~~~~~
Sincerely,
William Wang
Microsoft Partner Online Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
| Content-Class: urn:content-classes:message
| From: "Justin King" <jusking@.firstam.com>
| Sender: "Justin King" <jusking@.firstam.com>
| Subject: question on SQL schema
| Date: Tue, 12 Aug 2003 20:58:23 -0700
| Lines: 19
| Message-ID: <090d01c3614f$23f86d60$a401280a@.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcNhTyP4GoQlr1/wQteBwfzcYZvj7Q==| Newsgroups: microsoft.public.sqlserver.server
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:300961
| NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| My company is currently developing a new database driven
| application. Originally we had designed the structure
| with three database's (based on update schedule and
| administration). My Oracle DBA has looked over the plan
| and recomends we put the three databases into one and
| seperate as multiple schemas. I have looked all over
| microsoft.com and haven't found anything that discusses
| multiple schemas. I'm guessing its a different
| terminology on the Microsoft side.
|
| How would I go about developing a single database that is
| segregated into three so called schemas. I know Oracle
| uses [schema.]tablename and SQL uses
| server.database.owner.tablename. Does this mean I create
| an owner for each dataset (schema).
|
| thanks
|
| Justin
|

Question on index creation for large table

Hi all--Given a table with the current setup and structure, I would like to create a clustered index for a table that gets updated once per week values I would like to keep unique:

CREATETABLE [dbo].[DBA_AUDIT_SESSION](

[INSTANCE_NAME] [varchar](16)NULL,

[HOST_NAME] [varchar](64)NULL,

[OS_USERNAME] [varchar](255)NULL,

[USERNAME] [varchar](30)NULL,

[USERHOST] [varchar](128)NULL,

[TERMINAL] [varchar](255)NULL,

[TIMESTAMP] [datetime] NOTNULL,

[ACTION_NAME] [varchar](28)NULL,

[LOGOFF_TIME] [datetime] NULL,

[LOGOFF_LREAD] [float] NULL,

[LOGOFF_PREAD] [float] NULL,

[LOGOFF_LWRITE] [float] NULL,

[LOGOFF_DLOCK] [varchar](40)NULL,

[SESSIONID] [float] NOTNULL,

[RETURNCODE] [float] NOTNULL,

[CLIENT_ID] [varchar](64)NULL,

[SESSION_CPU] [float] NULL,

[Import_Date] [datetime] NULL

)

This is is for an existing table within a SQL Server 2005 SP1 database.

I was thinking about creating an IDENTITY column and creating that as a composite primary key with TIMESTAMP, but the following posting seems to suggest that's not a good idea for performance and not the best design:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=152846

Average size for this table is 1.75-2.5 million rows, and the TIMESTAMP field doesn't carry completely unique values. The data is sourced from an Oracle 9i and/or 10gR2 database, populated by an SSIS package. Suggestions on creating a good clustered index for this table? Would I gain value by using the NEWSEQUENTIALID function in SQL Server 2005 on a uniqueidentifier datatype column?

Thanks,

Jonathan

The primary question is why do you think that this table needs a 'clustered index'.

If you want to create a 'sequential' load, then clustering on an IDENTITY field is enough.

Typically, Audit tables have a minimal need for indexing -and it rarely has a primary factor of load sequence.

|||

Hi Arnie--I'm renovating the entire repository to factor in a couple of changes:

1. The audience for this data (manager, security & network administrator) commented on how slow queries returned data once three-four months' data accumulated in this repository.

2. I also have to store this on a VMWare-based virtual server with only 1 GB of RAM. (A request for additional resources was turned down a few months ago.) Given that, I'm looking to come up with the best possible indexing strategy with a need to store at least 3-6 months' data at a time, which leaves up to 11-12 million rows to query. That's an awful lot of data to sort through, so I'm trying to also create a long-term indexing strategy without reinventing the wheel for a server with fairly tight resource restraints.

Given there's a large amount of data that doesn't change more than once per month, a clustered index seems the best candidate for this table and another, similar table called 'dba_audit_trail', based on an Oracle-derived view. Thoughts?

- Jonathan

|||

I'd cluster index the [Timestamp] or [Import_Date] field (whichever one is capturing the datetime of the insert)-and NOT bother with an IDENTITY field.

(Duplicate [Timestamp] values are not an issue for the clustered index.)

IF all queries have a [Timestamp] criteria, I'd carefully examing other criteria filters to determine indexing the remaining fields.

|||OK--Thanks for the advice.

question on how to form a query

I have a question on a query I hope it is not a dim question.
Table structure
Table name: Subscriptioncustomer
Fields: CustomerId - int , SubID - int, TypeId - smallint, date
- datetime, userid - varchar
Table Name: Customer
Fields: CustomerID - int, LastName- varchar. FirstName - varchar,
SSN - char
Table Name: Address
Field Name: AddressID-int, address - varchar
Table Name: Archived_Address
Field name: AddressID - int, CustomerID - int, TypeID - smallint,
active -bit
Question is I need to format the queries that will insert a new
customer for SubID 95122. Once you have the new CustomerID, tie the
customer to AddressID 854268 with another insertHi
Check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data in a form that will be useful to someone replying to a message.
If you make your ID columns identity values then you could use the
SCOPE_IDENTITY() function to return the ID you have just inserted.
You description is not clear what the Archived_Address is for maybe this
should be the linking table for Customer and Address?
Ignoring Archive_address, then if you had the following table definitions
CREATE TABLE Customer (
CustomerID INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_Customer PRIMARY KEY
CLUSTERED,
LastName VARCHAR(128) NOT NULL,
FirstName VARCHAR(128) NOT NULL,
SSN CHAR(15) NULL )
CREATE TABLE Address (
AddressID INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_Address PRIMARY KEY
CLUSTERED,
address VARCHAR(500) NOT NULL )
CREATE TABLE Subscriptioncustomer (
CustomerId INT NOT NULL,
SubID INT NOT NULL,
TypeId SMALLINT NOT NULL,
[date] DATETIME NOT NULL CONSTRAINT DF_date DEFAULT GETDATE(),
userid VARCHAR(128) NOT NULL CONSTRAINT DF_userid DEFAULT USER_ID(),
CONSTRAINT PK_Subscriptioncustomer PRIMARY KEY CLUSTERED (CustomerId, SubID),
CONSTRAINT FK_Subscriptioncustomer_Customer FOREIGN KEY (CustomerID)
REFERENCES Customer (CustomerID)
)
CREATE TABLE Archived_Address (
AddressID INT NOT NULL,
CustomerID INT NOT NULL,
TypeID SMALLINT NOT NULL,
active bit,
CONSTRAINT PK_Archived_Address PRIMARY KEY CLUSTERED
(AddressID,CustomerID,TypeID ),
CONSTRAINT FK_Archived_Address_Customer FOREIGN KEY (CustomerID) REFERENCES
Customer (CustomerID),
CONSTRAINT FK_Archived_Address_Address FOREIGN KEY (AddressID) REFERENCES
Address (AddressID)
)
You could use the following procedure to add a new customer, address for
that customer and a subscription.
CREATE PROCEDURE spr_New_Customer_and_Subscription ( @.LastName VARCHAR(128),
@.FirstName VARCHAR(128),
@.SSN CHAR(15),
@.address VARCHAR(500),
@.SubID INT,
@.TypeId INT )
AS
BEGIN
SET NOCOUNT ON
DECLARE @.customerid int
DECLARE @.addressid int
DECLARE @.stat int
BEGIN TRANSACTION
INSERT INTO Customer ( LastName, FirstName, SSN )
VALUES ( @.LastName, @.FirstName, @.SSN )
SELECT @.customerid = SCOPE_IDENTITY(), @.stat = @.@.ERROR
-- Add Error Checking
INSERT INTO Address ( address )
VALUES (@.address)
SELECT @.addressid = SCOPE_IDENTITY(), @.stat = @.@.ERROR
-- Add Error Checking
INSERT INTO Subscriptioncustomer (CustomerId, SubID, TypeId )
VALUES (@.CustomerId, @.SubID, @.TypeId )
SET @.stat = @.@.ERROR
-- Add Error Checking
COMMIT TRANSACTION
-- Rollback if previous error
END
-- Test Run
EXEC dbo.spr_New_Customer_and_Subscription @.LastName = 'Bell',
@.FirstName = 'John',
@.SSN = 'N/A',
@.address = 'Somewhere',
@.SubID = 1,
@.TypeId = 1
-- Check it worked
SELECT * FROM Customer
SELECT * FROM Address
SELECT * FROM Subscriptioncustomer
If you want to pass an existing AddressID change the parameters to the
procedure and remove the insert into the Address table.
HTH
John
"shismith123@.gmail.com" wrote:
> I have a question on a query I hope it is not a dim question.
> Table structure
> Table name: Subscriptioncustomer
> Fields: CustomerId - int , SubID - int, TypeId - smallint, date
> - datetime, userid - varchar
> Table Name: Customer
> Fields: CustomerID - int, LastName- varchar. FirstName - varchar,
> SSN - char
> Table Name: Address
> Field Name: AddressID-int, address - varchar
> Table Name: Archived_Address
> Field name: AddressID - int, CustomerID - int, TypeID - smallint,
> active -bit
> Question is I need to format the queries that will insert a new
> customer for SubID 95122. Once you have the new CustomerID, tie the
> customer to AddressID 854268 with another insert
>

Question on Data/structure restore

new to SQL Server 2000. We have an obsolete database that we need to
save off for x number of years. DB2 has utilities (DB2Look/Export)
that allows for the export of the data along with a schema and script
that enables the future recreation of the structure of the databases
and tables to include RI etc. You can save off the architecture and
relationships of the tables as well as the data.

Does SQL Server have anything similar?

Failing that, our plan is to backup the data and logs then image the entire disk.

Thanks in advance.

GerryNot exactly sure what you want.

SQL Server 2000 of course has backup and restore capability, and the Enterprise Manager utility has the ability to script database objects and relationships.

A lot depends upon why you are archiving the data and what its intended use is.|||OK. If you had to save off a database...both the data and the 'structure' of the tables, relationships between tables etc....for possible recreation years down the road...how would you do it?|||sql server has a backup wizard, backup the DB to a BKF file and put it wherever, you can restore it just as easily with the wizard - table structures and all the data

also, the design of the database should be documented in the technical specs in a word doc. so you could recreate the whole thing from documentation if necessary|||Look up BACKUP in Books online

Do you have the SQL Server Client tools installed?|||Also look here

http://weblogs.sqlteam.com/tarad/archive/2004/08/04/1876.aspx|||also, the design of the database should be documented in the technical specs in a word doc. so you could recreate the whole thing from documentation if necessaryIn a Word document?
Just script the database ddl to a text file.|||I believe the scipting of the ddl of the objects in addition to a backup AND a data export is a desired redundancy. I belive the scripting capabilties of SS should do the job. That's for all the interesting info .

Gerry|||Should read Thanks :)

Question on column mappings between mining structure and case table for lift chart

Hi, all experts here,

I am a bit confused for the model evaluation (lift chart), should we map all the columns for both the mining structure and the case table? I mean for those predictive models, we have a predict column, shouldnt we ignore the mapping of the predictive column between the mining structure and the case table? But it seemes we are not allowed to miss the predictive column mapping between the mining structure and the case table.

Why is that? Could any experts here give me some explanation on that?

Hope my question is clear for your help.

Thanks a lot and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

The mapping of the predicted column is required to compare the prediction with the actual test data. It is not used in the actual prediction|||

Hi, Bogdan,

Thanks a lot for your advices.

With best regards,

Yours sincerely,