1

I have a requirement of creating nested tables in SQL server. Can any one guide me how to create them. Just to give a background I am trying to move the RDBMS from oracle to SQL server.

Structure of tables is as follows. I have table 'Employees' with address as one of the column. I have one more table with columns Street, Town, Dist, State. When I query the table 'Employees' I should see the attribute name and values of all the columns in address table in address column.

Employees: with columns: ID, FirstName, LastName, dept, gender, dob, address

Address (Nested table): with columns : Street, Town, Dist, State

This was done in oracle using Nested tables and user defined data types. Can any one suggest me what is alternative for this in SQL server. How can I achive this requirement in SQL server.

Improve this question
2
  • 1
    Why it is in separate tables? Can one employee have several addresses? Commented Jun 5, 2015 at 6:26
  • We do not want to maintain complete address in main table. If required only we will query the complete address of employee. Commented Jun 5, 2015 at 6:40

3 Answers 3

Reset to default
2

There are 3 cases:

  1. 1 to 0-1 relationship(pseudo one to one)
  2. 1 to * relationship(one to many)
  3. * to * relationship(many to many)

Now you should decide which one to choose.

If one employee can have multiple addresses you can go with option 2.

If one employee can have multiple addresses and one address can have multiple employees(from your data it sounds like it can be because 2 employees can live in the same town on the same street) then you should stick to option 3.

If neither of the above is true then just pick option 1.

Now the technical part for those options:

1) 

create table Employees(employee_id int, --will be PK
                       first_name varchar(50)
                       last_name varchar(50)
                       ....)

create table EmployeeAddresses(employee_id int, --will be PK and FK on Employees.employee_id
                               town varchar(50)
                               street varchar(50)
                               ....)

2)

If one employee can have multiple addresses:

create table Employees(employee_id int, --will be PK
                       first_name varchar(50)
                       last_name varchar(50)
                       ....)

create table EmployeeAddresses(address_id int, --will be PK
                               employee_id int, --will be FK on Employees.employee_id
                               town varchar(50)
                               street varchar(50)
                               ....)

If one address can have multiple employees:

create table Employees(employee_id int, --will be PK
                       address_id int, --will be PK and FK on Addresses.address_id
                       first_name varchar(50)
                       last_name varchar(50)
                       ....)

create table Addresses(address_id int, --will be PK 
                       town varchar(50)
                       street varchar(50)
                       ....)

3)

create table Employees(employee_id int, --will be PK
                       first_name varchar(50)
                       last_name varchar(50)
                       ....)

create table Addresses(address_id int, --will be PK
                       town varchar(50)
                       street varchar(50)
                       ....)

create table EmployeeAddresses(employee_id int, --will be PK and FK on Employees.employee_id
                               address_id int, --will be PK and FK on Addresses.address_id)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

I think the best solution is, when You create a separate table for adresses, and linked to Employees. For example:

create table [Employee]
(ID int not null
, FirstName varchar(100) not null
, LastName varchar(100) not null
--...
)
go
create table [Address]
(ID int IDENTITY(1,1) not null
, EmployeeID int not null
, Street varchar(200)
, Town varchar(200)
--...
)
go
ALTER TABLE [Address]  WITH CHECK ADD  CONSTRAINT [FK_Address_Employe] FOREIGN KEY(EmployeeID)
REFERENCES [Employee] ([ID])
go
Improve this answer

Comments

0

In SQL Server Analysis Services, data must be fed to a data mining algorithm as a series of cases that are contained within a case table. However, not all cases can be described by a single row of data. For example, a case might be derived from two tables: one table that contains customer information, and another table that contains customer purchases. A single customer in the customer information table might have multiple items in the customer purchases table, which makes it difficult to describe the data by using a single row. Analysis Services provides a unique method for handling these cases, by using nested tables.

In order to create a nested table, the two source tables must contain a defined relationship so that the items in one table can be related to the other table. In SQL Server Data Tools (SSDT), you can define this relationship in the data source view. For more information about how to define a relationship between two tables, see How to: Add, Delete, View, or Modify a Logical Relationship Using Data Source View Designer (Analysis Services).

https://technet.microsoft.com/en-us/library/ms174835(v=sql.110).aspx

Improve this answer

1 Comment

The question concerns Database Engine, not Analysis Services.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.