Modeling A Table With FK
Nov 1, 2006
I am having a problem when modeling a Foreign Key in an "Operations" table. This table holds all information on customers ´s applications and withdrawals.
Here is the structure:
CustomerID int, SourceID int, Value decimal (16,2), OperationDate datetime
Well the problem is that SourceID sometimes might be NULL depending on how the record was inserted. So its kind of cumbersome to define it as an FK, since it can be null...To get things worse, this SourceID might point to more than 1 table (depending on the CustomerType it will point to SourceA table or SourceB table)...
How should this be modeled?
View 1 Replies
ADVERTISEMENT
Nov 5, 2006
Can someone please explain this statement: At the logical level where there can be any number of entities in a relationship while physically you define relationships between two tables.
thx,
Kat
View 1 Replies
View Related
Feb 21, 2000
Are there any pitfalls when naming SQL Server 7.0 objects with underscores, caps, etc.?
View 1 Replies
View Related
Jul 11, 2007
Hello everybody,
I'm new to Sql.
Can anyone explain me
Data Modeling.
Thanks.
shiak bee.
View 3 Replies
View Related
Aug 25, 1998
Hi,
does anyone know of any data modeling tools that support or intend to support SQL 7.0 ?
I`d appreciate any comments ...
Kris Klasen
Act. Manager Data Warehouse Project
Information Management Branch
Department of Education, Training, Community and Cultural Development
E-mail: Kris.Klasen@Central.Tased.Edu.Au
http://www.tased.edu.au
Tel: 03 6233 6900
Fax: 03 6233 6969
73 Murray Street
2nd Floor
Hobart 7000
Tasmania
View 2 Replies
View Related
Jun 19, 2006
is there a link in this forum that speks about datamodeling or datawarehouse. iam looking for some help regarding the model that i have to build. i am not able to find a relation between different dimensions in terms of time which would be th e key to populate the fact table.
any help appreciated
View 1 Replies
View Related
Jul 23, 2005
I'm facing the next problem:I have a table with two columns (among others) modeling category andsubcategory data for each row. I need to summarize info on this twocolumns, but with the next specs:1.- Some grouping is only on the category column.2.- Some other grouping consider the two columns.The values for the two columns come from external source, i.e. I haveno means to know the precise universe of data (I suppose soon or laterwe'll have a sufficient sample of data, but for now it's not thecase). So, I would like to have a grouping table so it's not necessaryto insert a row for every pair of category and subcategory (althoughit would be the best approach for the sake of design's simplicity). AsI don't know every possible combination, I would prefer something like'this category is a - no matter the subcategory', and 'this othercategory + subcategory is b'. Let's go with a sample:--------------------------------------------------------Create Table B ( -- groupings ----categ char(8),subcateg char(5),what_group char(10))-- All rows with 432 code are cat. A ----Insert B ( '00000432', ' ', 'Category A' )-- All rows with 636 code are cat. C except when subcat is 8552 (cat.B) ----Insert B ( '00000636', '08552', 'Category B' )Insert B ( '00000636', ' ', 'Category C' )-- Some data ----Create Table A ( -- data ----categ char(8),subcateg char(5))Insert A ( '00000432', '01322' )Insert A ( '00000432', '01222' )Insert A ( '00000432', '01100' )Insert A ( '00000432', ' ' )Insert A ( '00000636', '08552' )Insert A ( '00000636', '08552' )Insert A ( '00000636', '01100' )Insert A ( '00000636', ' ' )Insert A ( '00000636', '01111' )-- The query like:Select b.what_group, count(*) as cntFrom aLeft Join bOn /* ? ? ? ? */-- Should give ---what_group cnt-------------- ----------Category A 4Category B 2Category C 3-------------------------------------------------------------------It would be easier knowing all the pairs categ - subcateg. If I don'tknow them, is a good idea to model the grouping table as I've donewith rows in B?TIA,DiegoBcn, Spain
View 1 Replies
View Related
Jul 20, 2005
Is there a good approach to modelling many heterogeneous entity typeswith that have some attributes in common?Say I have entities "employees" which share some attibutes (e.g.firstname, lastname, dateofbirth) but some subsets of employees (e.g.physicians, janitors, nurses, ambulance drivers) may have additionalattributes that do not apply to all employees. Physicians may haveattributes specialty and date of board certification, ambulancedrivers may have a drivers license id, janitors may havepreferredbroomtype and so on.There are many employee subtypes and more can be dynamically addedafter the application is deployed so it's obviously no good to keepadding attributes to the employees table because most attributes willbe NULL (since janitors are never doctors at the same time).The only solution I found for this is a generalization hiearchy whereyou have the employee table with all generic attributes and then youadd tables for each new employee subtype as necessary. The subtypetables share the primary key of the employee table. The employee tablehas a "discriminator" field that allows you to figure out whichsubtype table to load for a particular entity.This solution does not seem to scale since for each value of"discriminator" I need to perform a join with a different table. Whatif I need to retrieve 1,000 employees at once?Is that possible to obtain a single ResultSet with one SQL statementSQL?Or do you I need to iterate look at the discriminator and thenperform the appropriate join? If this kind of iteration is necessarythen obviously this generalization hierarchy approach does not work inpracticesince it would be painfully slow.Is there a better approach to modelling these kind of heterogeneousentities with shared attributes that does not involve creating a tablefor each new employee type or having sparce tables (mostly filled withNULLS)I guess another approach would be to use name/value pairs but thatwould make reporting really ugly.Seems like a very common problem. Any ideas? Is this a fundamentallimitation of SQL?Thanks!- robert
View 13 Replies
View Related
May 4, 2006
Good day.
i'm a new person here and not that familiar with T-SQL...
the question is: is there any buil-in functions or special libraries in T-SQL that can help to generate correlated random variables with non-normal distribution function?
It would be also good if someone could advice if there is any application (statistical programm or non-microsoft developed library) that can deal with MS SQL and has modeling and forecasting capabilities...
thanks in advance
View 6 Replies
View Related
Mar 4, 2014
I have a case study requesting to create an ER diagram, with the attributes listed in each entity. The data I have is an Excel Spreadsheet listing:
CustomerName
PurchaseDate
Destination
Airline
Flight#
departDate
DapartTime
ArriveTime
Hotel
CheckIn
CheckOut
Car Rental
Pickup
Return
The case is related to travel agency that specializes in booking interesting vacations for people who are single. Note that the travellers have a variety of travel bookings: some may rent a car and drive to the hotel at their destination, others may be staying with friends or relatives at their destination, while others will need flights, hotel and car rentals in their booking.
creation of the Tables and their attributes to Normalize the model to 3NF.
View 2 Replies
View Related
Feb 24, 2007
I'm trying to determine the best approach to model tables for a registration form that will be used temporarily and then taken offline once the event is over. I'd like to either model the tables so that they were reusable for other registration forms or perhaps use another method to store the data, maybe using XML or some other method if that is possible?? I'm not sure.
Different registration forms would have different input fields thereby requiring different table structures. It seems inefficient to create tables that will only be used temporarily and then no longer used. So, I would need to remember to delete the tables after they have been used or they would just take up space.
The basic requirement for this registration form is to allow the user to fill out the required fields, submit, get a registration reciept confirming their registration and allow the administrators for the event to pull the data weekly or daily into an excel spreadsheet.
I can create a flat table and a stored procedure that inserts the data. I can also write the dts package that exports the data to an excel file, which would require my intervention. I'd like to have something more automatic without my intervention.
Any suggestions would be appreciated. I'm not sure the best approach for this. Is using tables the best way to go even if the tables aren't re-usable? Requiring my attention to delete afterwards.
What are the options to generate the excel reports without my intervention?
Thanks in advance for any help.
View 1 Replies
View Related
Dec 27, 2006
I am running the Office Professional Plus 2007 RTM with all options enabled and SQL 2005 Developer Edition on my local box. Based on the system requirements listed on the download page for the Office 2007 Data Mining Add-In, I've also verified that I have the correct CTP of SQL 2005 SP2 and that .Net 2.0 Framework is installed. Finally, I've verified that my local instance of SQL Server is configured correctly to allow temporary data mining models.
In Excel, all of the Table Analysis tools seem to work fine, and most of the options on the Data Mining ribbon also work; however, all of the options under "Data Modeling" on the Data Mining ribbon return the following error when I try to use them:
"Could not load file or assembly 'Microsoft.DataWarehouse, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependents. The system cannot find the file specified."
I've tried uninstalling everything and reinstalling, but I still get this error when I try to use the Data Modeling options.
Right now, I'm only working against the sample data provided when the data mining add-in is installed.
Any ideas on how to resolve this issue?
View 7 Replies
View Related
Nov 20, 2006
Hello,
I need a tool that will let me model a SQL Server 2005 database and then generate the tables, constraints, etc. from the model. I've never used a modeling tool so my knowledge is quite limited. I don't need to model or reverse engineer an application - my sole concern is on the SQL Server database side. I'm not concerned if the tool integrates with Visual Studio. And, of course, price is one consideration.
Are there any good tools that I should look at?
Thanks,
BCB
View 3 Replies
View Related
Oct 8, 2007
Hi all
Probably not the right forum - pointers would be appreciated - but I'll give it a try anyway:
I'm in the process of designing a relational database to be used in a BI scenario - ie. dimension and fact tables. The data will eventually be used to feed cubes in Analysis services, however end users will probably be allowed to run reports aginst views of the relational database.
I'm currently looking at the employee dimensions and my first try would designate AGE as a SCD Type 2 attribute. As a result every employee gets at least one new record every year as AGE increases. Given that BIRTHDATE is specified should I drop AGE from the tables and recreate it as a computed attribute in database views and/or cubes?
Regards, Steen
View 7 Replies
View Related
Nov 30, 2006
Hello--
A question has come up around the following situation where a number of analysts will building data mining models in a specific analysis services databases.
- There is one AS DB for each modeling "project" and the analysts assigned to work on the "project" are grouped together in Windows Security Groups.
- The analysts are only allowed to access the AS DB for their project. To support this security model, we've implemented scripts to create the AS DB for the "project" and then a Role is created within the AS DB called "Administrator" and the members of this role are the members of the corresponding Windows Security Group.
The AS DB, role and membership are created by a machine "admin". After the AS DB is created, it appears that the only way an analyst can build models using Visual Studio (Business Intelligence Workbench) in the AS DB while maintaining the security model is to do the following:
- Run Visual Studio (or BI Workbench)
- Select File -> Open -> Analysis Services Database, then specify the database that they have access to.
In this "online" modeling environment, things are working fine. The question is -- is it possible for an analyst to create an Analysis Services Project in Visual Studio and "bind" it to already-created AS DB? This doesn't appear to work, but I may be missing something.
Thanks,
- Paul
View 1 Replies
View Related
Jun 17, 2015
Our business model involves a lot of dates and the business owners frequently want reports based on each of these different dates. For example in any given order there are as follows:
- Order created date
- Client due date
- Order first payment date (an order can have multiple payments)
- Order fully paid date
- Date assigned to vendor
- Vendor return date
- Date delivered to client
On top of that we have other areas of the business, the data from which ties into the above. Here we have more dates e.g.
- Date vendor recruited
- Date vendor reviewed
At any given point the manager may want a report based on any of these dates. For example;
- Product type by order creation date (fiscal year / month)
- Product type by first payment date  (fiscal year / month)
- Product type by client due date (fiscal year / month)
and so forth. I have been asked to create a report using all of the above on at least one occasion, many of them far more frequently. At the moment I have created a standard date table and then duplicated that for each type of date that I need however this is becoming excruciating to work with as I have approximately 10-12 date tables in my data model. Is there a better way of doing this now, in Excel 2013? If not, is there an improvement in 2016 that may make life easier?Â
View 6 Replies
View Related
Oct 19, 2015
I need a recommendation on a data modeling tool that can be used with a data warehouse. My warehouse is running SQL 2012.
Here is my challenge: Most of the tables in the warehouse do not have primary keys and none of the tables have foreign keys on them. However, there are indexes and unique keys/indexes on the tables. I am looking for a tool that I can create virtual relationships on how the data is related, so it is visually easier for the ETL developers to write the code.
I have looked at both ER/Studio 11 and ERwin 9.6. Neither of them do it exactly the way I want it too. However, ER/Studio is pretty close.
View 0 Replies
View Related
Jun 13, 2006
I am using SQL Server 2005 and trying to create a linked server on Oracle 10. I used the commands below:
EXEC sp_addlinkedserver
@server = 'test1',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'testsource'
exec sp_addlinkedsrvlogin
@rmtsrvname = 'test1',
@useself = 'false',
@rmtuser='sp',
@rmtpassword='sp'
When I execute
select * from test1...COUNTRY
I get the error. "The OLE DB provider "MSDAORA" for linked server "...." does not contain the table "COUNTRY". The table either does not exist or the current user does not have permissions on that table."
The 'sp' user I am connecting is the owner of the table. What could be the problem ?
Thanks a lot.
View 3 Replies
View Related
Jan 31, 2008
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50. insert into Table values('arun's',20) My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?
View 3 Replies
View Related
Mar 29, 2007
Hi
I am having a table called as status ,in that table one field is there i.e. currentstatus.
the rows which are having currentstatus as "ticket closed",i want to move those rows into other table called repository which is having same table structure as status table.
I can do programatically.
but is there any way for every 3 months system has to check and do this action means moving to repository table automatically?
Please help me.
Thanks.
View 1 Replies
View Related
Jul 24, 2015
I'm inserting from TempAccrual to VacationAccrual . It works nicely, however if I run this script again it will insert the same values again in VacationAccrual. How do I block that? IF there is a small change in one of the column in TempAccrual then allow insert. Here is my query
INSERT INTO vacationaccrual
(empno,
accrued_vacation,
accrued_sick_effective_date,
accrued_sick,
import_date)
[Code] ....
View 4 Replies
View Related
Nov 21, 2006
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
View 6 Replies
View Related
Jan 9, 2008
Hi all,
The following dbo.Tables of Northwind.mdf in my .SQLEXPRESS (SQL Server Management Studio Express) are missing:
dbo.Categories
dbo.CustomerCustomerDemo
dbo.CustomerDemographics
dbo.Customers
dbo.Employees
dbo.EmployeeTerritories
dbo.Order Details
dbo.Orders
dbo.Products
dbo.Regions
dbo.Shippers
dbo.Suppliers
dbo.Territories.
But, I have these dbo.Tables in a different Database "xyzDatabase". How can I copy each of these dbo.Tables to the another blank dbo.Table of Northwind Database?
I right clicked on the dbo.Categories and I saw the following thing:
dbo.Categories
New Table...
Modify
Open Table
Script Table as |> CREATYE To |>
DROP To |>
SELECT To |>
INSERT To |> New Query Editor Window
File....
Clipboard
UPDATE To |>
DELETE to |>
From the above observation,I think it is possible to copy the dbo.Table from the one Database to the Northwind Database that needs to be repaired. Please help and advise me how to do this task or tell me where I can find the Microsoft document that gives the details of this X-copy thing.
Thanks in advance,
Scott Chang
P. S. I am using VB 2005 Express to create a project to learn "Calling Stored Procedures with ADO.NET" (see Paul Kimmel's article in http://www.developer.com/db/article.php/3438221) that needs the dbo.Tables of Northwind Database and my Northwind Database has been screwed up for quite a while and needs a big repair.
View 3 Replies
View Related
Aug 29, 2007
which is more efficient...which takes less memory...how is the memory allocation done for both the types.
View 1 Replies
View Related
May 29, 2008
'****************************************************************************
Cmd.CommandText = "Drop Table Raj"
Cmd.ExecuteNonQuery()
Cmd.CommandText = "Select * Into Raj From XXX"
Cmd.ExecuteNonQuery()
'**************************************************************************
This generates error that Table already exist.
If Wait 1 sec then execute statement then it works fine.
Thanks in Advance
Piyush Verma
View 1 Replies
View Related
Dec 1, 2006
I have one control table to store all related table name
Table ID TableName
1 TableA
2 TableB
In Table A:
RecordID Value
1 1
2 2
3 3
In Table B:
RecordID Value
1 1
2 2
3 3
How can I get the result by select the Table list first and then combine the data in table A and table B?
Thank you!
View 1 Replies
View Related
Jan 26, 2006
Just wondering if there is an easy transact statement to copy table 1 to table 2, appending the data in table 2.with SQL2000, thanks.
View 2 Replies
View Related
Mar 20, 2008
Hi thanks for looking at my question
Using sqlServer management studio 2005
My Tables are something like this:
--Table 1 "Employee"
CREATE TABLE [MyCompany].[Employee](
[EmployeeGID] [int] IDENTITY(1,1) NOT NULL,
[BranchFID] [int] NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[MiddleName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeGID]
)
GO
ALTER TABLE [MyCompany].[Employee]
WITH CHECK ADD CONSTRAINT [FK_Employee_BranchFID]
FOREIGN KEY([BranchFID])
REFERENCES [myCompany].[Branch] ([BranchGID])
GO
ALTER TABLE [MyCompany].[Employee] CHECK CONSTRAINT [FK_Employee_BranchFID]
-- Table 2 "Branch"
CREATE TABLE [Mycompany].[Branch](
[BranchGID] [int] IDENTITY(1,1) NOT NULL,
[BranchName] [varchar](50) NOT NULL,
[City] [varchar](50) NOT NULL,
[ManagerFID] [int] NOT NULL,
CONSTRAINT [PK_Branch] PRIMARY KEY CLUSTERED
(
[BranchGID]
)
GO
ALTER TABLE [MyCompany].[Branch]
WITH CHECK ADD CONSTRAINT [FK_Branch_ManagerFID]
FOREIGN KEY([ManagerFID])
REFERENCES [MyCompany].[Employee] ([EmployeeGID])
GO
ALTER TABLE [MyCompany].[Branch]
CHECK CONSTRAINT [FK_Branch_ManagerFID]
--Foreign IDs = FID
--generated IDs = GID
Then I try a simple single row DELETE
DELETE FROM MyCompany.Employee
WHERE EmployeeGID= 39
Well this might look like a very basic error:
I get this Error after trying to delete something from Table €œEmployee€?
The DELETE statement conflicted with the
REFERENCE constraint "FK_Branch_ManagerFID".
The conflict occurred in database "MyDatabase",
table "myCompany.Branch", column 'ManagerFID'.
Yes what I€™ve been doing is to deactivate the foreign key constraint, in both tables when performing these kinds of operations, same thing if I try to delete a €œBranch€? entry, basically each entry in €œbranch€? and €œEmployee€? is child of each other which makes things more complicated.
My question is, is there a simple way to overcome this obstacle without having to deactivate the foreign key constraints every time or a good way to prevent this from happening in the first place? Is this when I have to use €œON DELETE CASCADE€? or something?
Thanks
View 8 Replies
View Related
Sep 17, 2007
In a table-valued UDF, does the UDF use a table variable or a temp table to form the resultset returned?
View 1 Replies
View Related
Nov 29, 2006
Banti writes "IF i create temporary table by using #table and ##table then what is the difference. i found no difference.
pls reply.
first:
create table ##temp
(
name varchar(25),
roll int
)
insert into ##temp values('banti',1)
select * from ##temp
second:
create table #temp
(
name varchar(25),
roll int
)
insert into #temp values('banti',1)
select * from #temp
both works fine , then what is the difference
waiting for ur reply
Banti"
View 1 Replies
View Related
Jun 9, 2014
I am having a problem in creating query for this exciting scenario.
Table A
ID ItemQtyCreatedDatetime
W001 CB112014-06-03 20:30:48.000
W002 CB112014-06-04 01:30:48.000
Table B
IDItemQtyCreatedDatetime
A001 CB112014-06-03 19:05:48.000
A002 CB112014-06-03 20:05:48.000
A003 CB112014-06-03 21:05:48.000
A004 CB112014-06-04 01:05:48.000
A005 CB112014-06-04 02:05:48.000
I would like to return the nearest date of Table B in my table like for
ID W001 in table B should return ID A002 CreatedDatetime: 2014-06-03 20:05:48.000
ID W002 in table B should return ID A004 CreatedDatetime: 2014-06-04 01:05:48.000
View 3 Replies
View Related
May 23, 2007
Is there any Posibility to change a User Table to System Table.
How to create one system table.
I am in Big mess that One of the Table I am using is in System Type.
I cant Index the same. Is there any Mistake we can change a user table to system table.....
View 9 Replies
View Related
Sep 9, 2005
I have a column defined as smalldatetime. Default length (4), and "allow NULLS" is checked.In the Enterprise Manager UI, when i enter data into that table row, if i just tab past that column, all is well, and the value is represented in the UI as <NULL>.The problem comes once i ever enter a date into that column. Say i have entered a date (all is well), and now i want to remove that entry and go back to NULL (after the date value has been committed, different entry session, say).How is that done?It seems to me, once a date has ever been entered into that column, now, if i try to remove it, i get the error "The value you entered is not consistant with the data type or length of the column, or over grid buffer limit". I have tried deleting the value, entering spaces, entering the string NULL or the string <NULL>; maybe some other tries as well, but none works, i always get that error message and am not allowed to proceed past that cell until i restore a date value to it. I want to get back to <NULL>.Anybody know?Thank you.Tom
View 1 Replies
View Related
