Create Permanent Association Between Two Tables

May 17, 2015

I've two tables in SQL Server 2014, in database named cnc

I wish to create a permanent association between the two tables, with referential integrity.  I cannot find, in all the books I've gone through, nor the websites I've visited, how to do this.

Table 1:
name: dbo.p
fields in table 1:
pID    autoenumerating, indexed
p_nm_l   varchar(25)
p_nm_f   varchar(25)

Table 2:
name: dbo.pe
fields in table 2:
pe_ID BigInt
pe_ssn1_
pe_ssn2_
pe_ssn3_
pe_dobm_
pe_dobd_
pe_doby_

I wish to associate - with a one to one - pID with pe_ID with pID as the primary field.  I've no clue how to do this.

View 3 Replies


ADVERTISEMENT

Does 7.0 Create Permanent Indexes On Its Own??

Oct 29, 1999

Hello,

A few months ago, the company I work for switched to SQL 7.0 from 6.5. All is running fine but recently I noticed that we have a ton of indexes on each table that start like this: _WA_SYS_....0B679CE2 (here are some specific examples: _WA_SYS_PRODUCT_LINE_0B679CE2, _WA_SYS_RMA_DISPOSITION_0B679CE2)

Did SQL 7.0 do this? If so why? What is it trying to do? Why doesn't it use current indexes (clustered and non-clustered)? Why don't they get Fragmented like User defined indexes? I have a lot of questions associated with this if someone can explain it.

Tech Net, MSDN, BOL and other manual resources don't mention anything about it. At least I haven't found anything on it and I have spent some time looking.

Please help if you can.
Troy

View 2 Replies View Related

Is Possible To Create A New Permanent Table By A Procedure?

Mar 23, 2007

Is possible to create a new permanent table by a procedure? I mean not a temporary table. If it is how do I do with the name? Because I would like put for each new table created as name as "forum1", "forum2", etc ... and not just numbers as I'll show you in the example. I tried to create the procedure, but I got stuck with permissions e primary key.
create procedure new_forum
 
@user_id int,
@title varchar(50),
@description varchar(200)
 
as
begin
 
declare @totalForuns int
 
select @totalForuns = count(*) from foruns
 
set @totalForuns = @totalForuns + 1
 
Insert into foruns (title,creation,country,[views],[description])
      values (@title,getdate(),'England',0,@description)
 
CREATE TABLE [dbo].[@totalForuns](
      [thread_id] [int] IDENTITY(1,1) NOT NULL,
      [user_id] [int] NOT NULL,
      [last_user_id] [int] NOT NULL,
      [title] [nvarchar](50) NOT NULL,
      [creation] [datetime] NOT NULL,
      [last_answer] [datetime] NOT NULL,
      [answers] [int] NOT NULL CONSTRAINT [DF_threads_answers]  DEFAULT ((0)),
      [vizuais] [int] NOT NULL CONSTRAINT [DF_threads_views]  DEFAULT ((0)),
      [fixed] [bit] NOT NULL CONSTRAINT [DF_threads_fixed]  DEFAULT ((0)),
      [votos] [int] NOT NULL CONSTRAINT [DF_threads_votos]  DEFAULT (''),
 CONSTRAINT [PK_threads] PRIMARY KEY CLUSTERED
(
      [@totalForuns] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
ALTER TABLE [dbo].[@totalForuns]  WITH CHECK ADD  CONSTRAINT [FK_threads_users] FOREIGN KEY([user_id])
REFERENCES [dbo].[users] ([user_id])
GO
ALTER TABLE [dbo].[@totalForuns] CHECK CONSTRAINT [FK_threads_users]
 
end
Go
 
Thank you very much.
 

View 5 Replies View Related

Case Table And Nested Tables In Association Algorithm

Aug 30, 2006

hi

 

i m trying to build microsoft association model using Microsoft association algorithm. i got

1) patient table(patientid, name, city)

2) diseases(diseaseid, dieseasename)

It is M:N [many to many] relationship between above tables, so

3)Patient_diseases(patientid,disease_id). [RELATIONSHIP TABLE]

 i am trying to associate city in patient table --> disease in diseases table.   I want to build association data mining model and use it on web form, such a way when the user enters city associated disease will be displayed.

should i select all 3 table to build the model? could help me to decide what tables should i select as Case and what tables as Nested? what attributes from the table should i select as key, input, predictive ?

i am using data mining tutorials on sqlserverdatamining.com to build this model. is there anything further during my model building i get into confusion? please suggest me where i can find complete resource or inform here.

i appreciate Mr.Jamie for his guidance so far in my academic project. i do have the book 'Data mining with sql server 2005'.  I left with just one day to do this and document.

hoping someone could suggest. your help is much appreciated.

regards

raju

View 4 Replies View Related

Excellent Permanent Job Based In Glasgow Scotland.

Mar 23, 2001

I have an outstanding opportunity for a talented Senior Support Analyst. You must have Transact-SQL, Sybase or Oracle, Visual Basic, with Powerbuilder or C being "nice to haves".
You will be working with our existing developers and provide 2nd level support. You will also work alongside our business analysts and so you will need to understand the business area.
Basic salary of £28,000 gbp(sterling) to £38,000 gbp (sterling) depending on skill level and experience.
Please note in this instance you must posses a current and valid UK (United Kingdom) work permit or be a UK national.
We offer exceptional career development for the successful candidate.
Contact me now...Steve@geordielad.demon.co.uk

emails only at this stage to save non specific phone calls.

View 1 Replies View Related

ASP Authentication With SQL Server 2005 - Not Express, Permanent Change

May 8, 2006

Hi all,
I've read up on the fact that if you use SQL server 2005 instead of SQL express you need to change the connection string in web.config.
Is there anyway to make this default connection type, or do I have to do it for every website I create. i.e. is there a global file that this connection can be entered into. Then whenever I start a new website I don't have to put the string in.
Just me being lazy really. But it's nice to know if it's possible.
 
Many thanks
DrB2k2

View 2 Replies View Related

Updating A Permanent Table From A Dataset Within The Data Flow

Feb 14, 2007

I have a dataset that was created via a source + lookups + derived columns.

I wish to take this dataset and treat is as a table within a sql statement so that I can update a permanent table with the a specific value within the temp dataset.

In sql this is what I am trying to do:

SELECT COUNT(*) AS Count_of_Employees, DEPT
FROM Employees
GROUP BY DEPT

UPDATE Departments
SET Number_of_Employees = Count_of_Employees
WHERE Dept = dept.

View 1 Replies View Related

Solution!-Create Access/Jet DB, Tables, Delete Tables, Compact Database

Feb 5, 2007

From Newbie to Newbie,



Add reference to:

'Microsoft ActiveX Data Objects 2.8 Library

'Microsoft ADO Ext.2.8 for DDL and Security

'Microsoft Jet and Replication Objects 2.6 Library

--------------------------------------------------------

Imports System.IO

Imports System.IO.File





Code Snippet

'BACKUP DATABASE

Public Shared Sub Restart()

End Sub



'You have to have a BackUps folder included into your release!

Private Sub BackUpDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUpDB.Click
Dim addtimestamp As String
Dim f As String
Dim z As String
Dim g As String
Dim Dialogbox1 As New Backupinfo


addtimestamp = Format(Now(), "_MMddyy_HHmm")
z = "C:Program FilesVSoftAppMissNewAppDB.mdb"
g = addtimestamp + ".mdb"


'Add timestamp and .mdb endging to NewAppDB
f = "C:Program FilesVSoftAppMissBackUpsNewAppDB" & g & ""



Try

File.Copy(z, f)

Catch ex As System.Exception

System.Windows.Forms.MessageBox.Show(ex.Message)

End Try



MsgBox("Backup completed succesfully.")
If Dialogbox1.ShowDialog = Windows.Forms.DialogResult.OK Then
End If
End Sub






Code Snippet

'RESTORE DATABASE

Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles

RestoreDB.Click
Dim Filename As String
Dim Restart1 As New RestoreRestart
Dim overwrite As Boolean
overwrite = True
Dim xi As String


With OpenFileDialog1
.Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*"
If .ShowDialog() = Windows.Forms.DialogResult.OK Then
Filename = .FileName



'Strips restored database from the timestamp
xi = "C:Program FilesVSoftAppMissNewAppDB.mdb"
File.Copy(Filename, xi, overwrite)
End If
End With


'Notify user
MsgBox("Data restored successfully")


Restart()
If Restart1.ShowDialog = Windows.Forms.DialogResult.OK Then
Application.Restart()
End If
End Sub








Code Snippet

'CREATE NEW DATABASE

Private Sub CreateNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles

CreateNewDB.Click
Dim L As New DatabaseEraseWarning
Dim Cat As ADOX.Catalog
Cat = New ADOX.Catalog
Dim Restart2 As New NewDBRestart
If File.Exists("C:Program FilesVSoftAppMissNewAppDB.mdb") Then
If L.ShowDialog() = Windows.Forms.DialogResult.Cancel Then
Exit Sub
Else
File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
End If
End If
Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;

Jet OLEDB:Engine Type=5")

Dim Cn As ADODB.Connection
'Dim Cat As ADOX.Catalog
Dim Tablename As ADOX.Table
'Taylor these according to your need - add so many column as you need.
Dim col As ADOX.Column = New ADOX.Column
Dim col1 As ADOX.Column = New ADOX.Column
Dim col2 As ADOX.Column = New ADOX.Column
Dim col3 As ADOX.Column = New ADOX.Column
Dim col4 As ADOX.Column = New ADOX.Column
Dim col5 As ADOX.Column = New ADOX.Column
Dim col6 As ADOX.Column = New ADOX.Column
Dim col7 As ADOX.Column = New ADOX.Column
Dim col8 As ADOX.Column = New ADOX.Column

Cn = New ADODB.Connection
Cat = New ADOX.Catalog
Tablename = New ADOX.Table



'Open the connection
Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;Jet

OLEDB:Engine Type=5")


'Open the Catalog
Cat.ActiveConnection = Cn



'Create the table (you can name it anyway you want)
Tablename.Name = "Table1"


'Taylor according to your need - add so many column as you need. Watch for the DataType!
col.Name = "ID"
col.Type = ADOX.DataTypeEnum.adInteger
col1.Name = "MA"
col1.Type = ADOX.DataTypeEnum.adInteger
col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col2.Name = "FName"
col2.Type = ADOX.DataTypeEnum.adVarWChar
col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col3.Name = "LName"
col3.Type = ADOX.DataTypeEnum.adVarWChar
col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col4.Name = "DOB"
col4.Type = ADOX.DataTypeEnum.adDate
col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col5.Name = "Gender"
col5.Type = ADOX.DataTypeEnum.adVarWChar
col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col6.Name = "Phone1"
col6.Type = ADOX.DataTypeEnum.adVarWChar
col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col7.Name = "Phone2"
col7.Type = ADOX.DataTypeEnum.adVarWChar
col7.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col8.Name = "Notes"
col8.Type = ADOX.DataTypeEnum.adVarWChar
col8.Attributes = ADOX.ColumnAttributesEnum.adColNullable



Tablename.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "ID")


'You have to append all your columns you have created above
Tablename.Columns.Append(col)
Tablename.Columns.Append(col1)
Tablename.Columns.Append(col2)
Tablename.Columns.Append(col3)
Tablename.Columns.Append(col4)
Tablename.Columns.Append(col5)
Tablename.Columns.Append(col6)
Tablename.Columns.Append(col7)
Tablename.Columns.Append(col8)



'Append the newly created table to the Tables Collection
Cat.Tables.Append(Tablename)



'User notification )
MsgBox("A new empty database was created successfully")


'clean up objects
Tablename = Nothing
Cat = Nothing
Cn.Close()
Cn = Nothing


'Restart application
If Restart2.ShowDialog() = Windows.Forms.DialogResult.OK Then
Application.Restart()
End If

End Sub








Code Snippet



'COMPACT DATABASE

Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles

CompactDB.Click
Dim JRO As JRO.JetEngine
JRO = New JRO.JetEngine


'The first source is the original, the second is the compacted database under an other name.
JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program

FilesVSoftAppMissNewAppDB.mdb; Jet OLEDB:Engine Type=5", "Provider=Microsoft.Jet.OLEDB.4.0;

Data Source=C:Program FilesVSoftAppMissNewAppDBComp.mdb; JetOLEDB:Engine Type=5")


'Original (not compacted database is deleted)
File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")


'Compacted database is renamed to the original databas's neme.
Rename("C:Program FilesVSoftAppMissNewAppDBComp.mdb", "C:Program FilesVSoftAppMissNewAppDB.mdb")


'User notification
MsgBox("The database was compacted successfully")

End Sub

End Class

View 1 Replies View Related

How To Create Multiple Tables On The Fly So That Every User Each Has His/her Own Set Of Tables?

Aug 12, 2006

Hello all,

Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients.

Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result.

What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1

Please help :-D

Greetingz,

DJ Roelfsema

View 6 Replies View Related

How To Basically Copy Tables With New Names Rather Than Create Similar Tables From Similar Manual Input.

May 26, 2007

I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?Thanks in advance. 

View 4 Replies View Related

How To Transform An .MDF Database Into A Permanent Database In Sql Server Express?

Apr 10, 2007

Hi, i have a MDF file used by an asp.net application (with sql server 2005 express).
I want to make it to a permanent database in sql server express.
I know that i can attach it from Management Studio, but is there no better way?
Does it exist a way to 'import' or 'migrate' or copy' a .MDF.database into the central sql server, as if created directly in sql server express? After that, i can delete the .MDF file.
Thanks
Tartuffe

View 3 Replies View Related

Copying Temp Table Data To Permanent Table

Nov 23, 2007

Hello guys..

Can u plz help me by giving me an idea how i can copy the temp table data to permanent table

Thanks,
sohails

View 1 Replies View Related

Global Temp Table Vs. Permanent Table Use

Dec 17, 2004

I need to decide what is better to use: global temp table ( I can't use local one) or permanent table in SQL 2000 stored procedures. I extract data from linked server table and update several tables on our server.
Those procedures scheduled to run every 3 hours.

Another question: for some reasons when I used global temp table, I wasn't able to schedule multi steps with every step executing one of the stored procedures.I think global temp tables should be visible to other stored procedures, right?

Your suggestions?

View 1 Replies View Related

How To Create Tables?

Feb 5, 2000

Hi All,

I am a serious NT sql NEWBIE. I have installed v7 on my NT/MIIS. I want to allow
another party now to create tables on their SQL db they have runnning on my
server (their own domain)

What is the best way -? Do they send me the scripts or is there a way to run them
remotely and create them.

If they do send me the scripts what do I do?

Regards
Amy

View 2 Replies View Related

How To Create Log Tables

May 9, 2008

hi everybody

Can anyone tell me how to crate log tables and how to insert value into log tables?

View 3 Replies View Related

How To Create Such Tables??

Mar 23, 2006

hello all...

i want to drop table 2Autonumber ---- table name but when i run query i am getting following error

drop table 2Autonumber

Error : Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '2'.

also know how to create such tables??

T.I.A

Papillon

View 2 Replies View Related

Factorized Association

May 16, 2007

Hello,
Let€™s say (for simplicity), in my site you can do one of two things €“ look at products and buy products.
I want to build an association structure between my products based on those two actions, but(!) when a user looks at two products it creates less important association than when the user actually bought those two products.
So basically, I want to give a different factor base on different actions occurred on my products.
How do I build my structure? How do I query it?

View 3 Replies View Related

Personalization Rather Than Association

May 14, 2007

I'm trying to figure out how to build a personalization engine.

If my structure is built with users as case, and products as nested - I€™d like to predict best products per user (rather than associated products), and If possible, ignoring products he already bought.



How do I do it?

View 7 Replies View Related

Use Of Ms Association Algo.

Jul 24, 2007

Hi,

I am working on a table that has following fields transaction_id, product_name,product_brand,product_size,product_quantity.

fyi, If a customer purchases 3 items, all have same transaction_id.

I need to use this table (in BIDS) for finding associations between different products,, but I am unable to do so.

Can anyone help me as to which fields should be used as input so that I can predict the association.

Thanks a lot.



Aashutosh Magdum

View 6 Replies View Related

HELP!!:about The MS Association Rules

May 14, 2006

When i use the MS association rules ,i don't know how it is worked on the background .I stuy the Fp-Growth algorithm , but there're some questions , I don't kown what's the meaning of transcation database. who can give me one example ? thanks .I know we can store the data in relation database,but in basket Analysis ,how a transaction stroed in relation database?

View 3 Replies View Related

How To Detect *.cer Association

May 29, 2007

I am entering to administration of
SS2005 SP1 (Windows 2003) having files mdf, ndf, ldf in
C:Program FilesMicrosoft sql serverMSSQLData
This dir also has two *.cer files.

Apparently no encryption is used


How can I get known what these *.cer files are for?

View 4 Replies View Related

Association Scenario

Aug 10, 2006

Hi,
I have a product basket scenario in where I have to recommend contracts to a customer based on the product and the quantity he/she buys. Product Quantity is an important factor which administers the user in the purchase of a particular contract
I have the following tables with me.
Customer product transaction table, Customer Contract transaction table but there is no direct relationship between contract and product in the database. The only way the two can be linked is through the customer.
If I create a mining structure with Customer-Product information as the nested table and Customer-Contract information as the nested table with customer being the link between the two, the model is showing some irreverent contract recommendations.
what is the solution for the above problem? Is it because the is no direct relationship between the product and the contract?
How can I overcome this problem?

View 6 Replies View Related

Association Rules

Dec 3, 2006

1) I use the identifier of transaction and attribute in one table.

Do I can to build a association rules structure without the use of the nested tables?

I tried - did not turn out...

2) As it is necessary to use a main and child table, can not build a prediction query.

When I try to add the predict column in a criteria/argument (Field=PredictSupport), i'll given message:

"Nested table column cannot be used as an argument in a data mining function."

I can not use other columns, because they are not predicable.

View 4 Replies View Related

Association Questions

May 31, 2007

I'm wondering if anyone can give me some help with an association model I'd like to setup. It's a typical market-basket analysis, but rather than grouping by individual customers, I'd like to group by customer grouping. (In our database, customers are grouped into categories like: large, small, medium) If this is possible, I'd like to generate the most popular items (so just querying the most probable itemsets), for each customer grouping (I'll refer to this as 'segments' from here on out), and then create a listing of customers in each segment which do not have the most popular items for their segment. I know for this last part I can use reporting services to tackle that problem, however, I'm not really sure how I can really do the rest of this with an association model in SSAS.



Our table structure looks like this:




Code Snippet

CustomerTable PurchasesTable

------------- --------------

CustomerName(key) CustomerName

CustomerGroup PurchasedProduct



And the data is arranged in this fashion:



Code SnippetCustomer Table:
CustomerName CustomerGroup
------------- -------------
A large
B large
C small

Purchases Table:
CustomerName PurchasedProduct
------------ ----------------
A ProductA
A ProductB
B ProductA
C ProductC
C ProductD



I know this is a lot of information but any help you guys may be able to offer would be great! Thanks!

View 5 Replies View Related

Create Tables Dynamically

Sep 12, 2007

Hi! I am using VS 2003 (v 1.1)
I need to generate tables with the values from the Backend (SQLServer 2000) database in C#.Net.
How can i create the tables, tablerows, cells, etc. from the codebehind page of C#. I am having a very little knowledge about dynamic generation.
Give me the complete code with can example (if possible)
 
Thanks & Regrads
Jai Shankar
 
 
 

View 1 Replies View Related

Create New Sql Tables Correctly

Dec 12, 2007

Hi,

View 6 Replies View Related

Create Tables With Other Different Prefixes

Jun 7, 2008

I was  just looking at the AdventureWorks Database Sample and I see the Tables and the Procedures have different prefixes other than dbo. How can I create Tables that use different prefixes as I would like to organize my tables better. Thanks in advance.

View 2 Replies View Related

How To Create 3 Users Tables?

Dec 21, 2005

Hello,In my web site I have 2 types of users: students and professors.All users need to login but the personal information for each user type has different fields.So I create 3 tables: professors, students and authentication,"professors" will have all the personal data from professors."students" will have all the personal data from students.authentication will have the username and password from all users.How can i link authentication table to both professors and students tables so when the user logins i get the data from that user, either a professor or student?And is the right way to do this?I am using SQL 2005 and Asp.Net 2.0Thanks,Miguel

View 1 Replies View Related

Create Views Not Tables

Jun 9, 2004

I want to allow a group of users to create views but not be able to create new tables or stored procedures... how can I do this ??

Thanks, John :eek:

View 1 Replies View Related

Create Tables Under Different Users.

Nov 30, 2004

I know this may sound stupid but I really can't figure out how to do it. I just keep going around in cricles.

I need to create six table under three different users. so I would have users1 owning tableA and tableB. Then I would have users2 owning another copy of tableA and tableB. same for users3.

But I just can't find a way of doing it, I am using enterprise manager. Please help, Ed

View 5 Replies View Related

Help Create Query Against Two Tables

Apr 5, 2006

stepdefinition has
steptype
flowid
stepid

task has
taskid
flowid
stepid

flowid and stepid for both tables match; meaning that if i found a record in task with a certain taskid, i could query stepdefinition with the same flowid and stepid to find the steptype.

well, i wanna do it the other way around. I query stepdefinition to find a list of flowids and stepids for a specific steptype.

select flowid, stepid from stepdefinition where steptype = -3

Now, I want to find all taskids in task for each flowid/stepid combination

here's a visual

http://www.filecabin.com/up1/1144249279-task.gif

View 2 Replies View Related

Need Help Figuring Out How Many Tables To Create

Apr 17, 2006

Hi, I have a users table in my SQL Server database. Now, I am looking to create a table (or multiple tables) to allow users to post their weekly events, meetings, activities, and accomplishments to the database. Each Monday morning, each user will enter their new schedule for that week and the previous week's entry will be archived in the database. My question is: what would make more sense? Should I create one big table that would have the following columns:

- Week Number (the current week number in the year)
- User ID
- Events
- Meetings
- Activities
- Accomplishments

And each user would have one row in the database per week.

OR should I create 4 separate tables named Events, Meetings, Activities, Accomplishments. Each of these tables would have the following columns: (for instance, the Events table would contain:)

- eventID (auto incremented number)
- userID
- weekNumber
- event_description

Each time a user adds a new event to their schedule, a new row in the Events table is created. Each time a user adds a new accomplishment, a new row in the Accomplishments table is created. etc., etc.

Which approach seems to make more sense and would be easier to maintain? Also, which one would conserve database space better and result in faster querying.

Thanks, Dave

View 11 Replies View Related

How Can I Create A History Of Tables?

May 31, 2006

I have to create a history of tables. Therefore I must know the previous structure of the table. I have to document the changes of the structures of the tables.
Is there a solution for the version 2000?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved