10 Replies - 714 Views - Last Post: 19 December 2012 - 01:43 PM Rate Topic: -----

#1 flexin1981  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 11-September 12

.sdf tables question

Posted 19 December 2012 - 03:59 AM

Hi Guys,

I have a table which holds employee data, i.e address etc what i would like to do is create a new table programmatically and attach it to each of the records in the database.

so for instance can i have one table that holds the names and address info and then for each record have a table attached that holds past pay information,tax etc.

is this possible or do i need to hold everything in one table.

Thanks in advance.

Flexin

Is This A Good Question/Topic? 0
  • +

Replies To: .sdf tables question

#2 Momerath  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1010
  • View blog
  • Posts: 2,444
  • Joined: 04-October 09

Re: .sdf tables question

Posted 19 December 2012 - 07:07 AM

You want a separate table for each record in the first table? That's just crazy. Use one table to hold related information.
Was This Post Helpful? 2
  • +
  • -

#3 flexin1981  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 11-September 12

Re: .sdf tables question

Posted 19 December 2012 - 07:35 AM

The reason i want to hold a secondary table for each record in the first is that as time goes on the second table is going to grow as time goes on as rows are added based on the date, so if the second holds the large data it would make searches on the first table quicker.

Also for each record in the first table the secondary table would not hold the same Column names or or amount of columns. These tables would be unique per record. So if they were held all in the one table i could see that table become pretty unrully very quickly.

So is it possible to attach a table to a record within a table or am i going to have to make the association between the tables based a naming convention stored in a column in the first table. therefore my program would know where to look for the attached table. I guess the question is there a natural table relationship or do i have to make it for myself.

Thanks

Flexin
Was This Post Helpful? 0
  • +
  • -

#4 MrShoes  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 312
  • View blog
  • Posts: 488
  • Joined: 13-June 12

Re: .sdf tables question

Posted 19 December 2012 - 08:18 AM

This is possible. It's just plain wrong. It goes against everything relational databases were created for.

Create an Employee_ID field as a primary key of your employees table. Each database record can then link via secondary key back to the employee details. So, if you want that employee's transaction details, you could use

SELECT a.*, b.EmployeeName FROM dbo.Transactions AS a INNER JOIN dbo.Employees AS b ON a.Employee_ID = b.Employee_ID WHERE b.EmployeeName = 'Flexin' 

Was This Post Helpful? 2
  • +
  • -

#5 Momerath  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1010
  • View blog
  • Posts: 2,444
  • Joined: 04-October 09

Re: .sdf tables question

Posted 19 December 2012 - 08:50 AM

Sounds like you don't understand relational databases or how to normalize data. Why don't you tell us what kind of data you need to store and we can help you develop tables to hold this data.
Was This Post Helpful? 1
  • +
  • -

#6 flexin1981  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 11-September 12

Re: .sdf tables question

Posted 19 December 2012 - 09:19 AM

your absolutely right i'm not great with DB's :blush:

what i need to do is hold employee names addresses etc

then attached to that employee is "KPI" data, this data is different per user and would be added bespoke to each employee by an admin each KPI is then tracked over time.

So example we have employee 1 which has five KPI's tracked monthly over 5 years. Then we have employee 2 which has 10 completely different KPI's tracked over a year.

thanks for the help

Flexin
Was This Post Helpful? 0
  • +
  • -

#7 MrShoes  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 312
  • View blog
  • Posts: 488
  • Joined: 13-June 12

Re: .sdf tables question

Posted 19 December 2012 - 09:40 AM

Simplest way to do this is to have an "Employees" table, with a primary key of EmployeeID, or some other unique field that identities the employee. The table could hold their personal details. Next, you could have a "KPIs" table, which has a row for each KPI, and an EmployeeID column, so it can be linked back to the user. EG

Employees

-------------------------------------------------------------
|EmployeeID |Name | Address |
-------------------------------------------------------------
| 00001 | MrShoes | Dark House, Dark Street |
| 00002 | Flexin | Elsewhere, Not Sure Where |
-------------------------------------------------------------

KPIs

------------------------------------------------------------------------
|EmployeeID |KPI |Description |Months |
------------------------------------------------------------------------
| 00001 | Perform better | Must try harder. | 12 |
| 00002 | Keep learning | Visit DIC more often. | 36 |
| 00002 | Build program | Make a really cool program. | 24 |
------------------------------------------------------------------------

Now, if you wanted to see KPIs for Flexin, you would use:
SELECT b.Name AS Employee, a.KPI, a.Description, a.Months FROM dbo.KPIs AS a INNER JOIN dbo.Employees AS b WHERE b.Name = 'Flexin', which would return:
------------------------------------------------------------------------
|Employee |KPI |Description |Months |
------------------------------------------------------------------------
| Flexin | Keep learning | Visit DIC more often. | 36 |
| Flexin | Build program | Make a really cool program. | 24 |
------------------------------------------------------------------------

EDIT: Sorry the spaces aren't working properly!

This post has been edited by MrShoes: 19 December 2012 - 09:46 AM

Was This Post Helpful? 0
  • +
  • -

#8 flexin1981  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 11-September 12

Re: .sdf tables question

Posted 19 December 2012 - 10:44 AM

Thanks Guys,

I appreciate the input, maybe i am missing the point here but the solution you have proposed is very similar to what i had in mind although i would have more tables. See Below;

Employees (Table Name)
-------------------------------------------------------------
|EmployeeID |Name | Address |
-------------------------------------------------------------
| 00001 | MrShoes | Dark House, Dark Street |
| 00002 | Flexin | Elsewhere, Not Sure Where |
-------------------------------------------------------------

00001(Table Name)
-------------------------------------------------------------
Date |KPI1 |KPI2 | KPI3 |
-------------------------------------------------------------
| 10/12 | 60% | 70% | 100% |
| 11/12 | 65% | 75% | 98% |
-------------------------------------------------------------

00002(Table Name)
-------------------------------------------------------------
Date |KPI1 |KPI2 | KPI3 |
-------------------------------------------------------------
| 10/12 | 70% | 40% | 10% |
| 11/12 | 95% | 65% | 14% |
-------------------------------------------------------------


I thought this would scale better as if i had 2000 employees then instead of searching through 10,000's of records looking for all that employee's past KPI's i could look for that employee's table based on the unique employee id as the table name then everything in that table is KPI info for that employee increasing the efficiency of the search ?.

I thought there might be a native way other than this "unique ID" to associate the table with the employee. similar to the concept of an array of array's

Thanks for taking the time to help me work through this.

Flexin
Was This Post Helpful? 0
  • +
  • -

#9 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4479
  • View blog
  • Posts: 7,801
  • Joined: 08-June 10

Re: .sdf tables question

Posted 19 December 2012 - 11:58 AM

Databases scale better with records than it does tables. Your tens of thousands of records is nothing to SQL Server, it can handle much larger data. Having a table for each person is inefficient not only in DB performance, but in terms of writing queries. Imagine the stored procedures or queries you'd have to write that would determine what table to look in? You'd have to dynamically create each query instead of having a fixed query that has parameters. It's better to have the one table and use a foreign key to say who it's related to.

Also, you can index the data based on that FK, which will make it lightning fast to query against. If you had individual tables, you'd have to index each table, which would increase the overhead. edit: I actually don't know if you can index SQL CE databases. I'll look that up. yes, you can index SQL CE tables.

SQL also does its own query optimization, so using a standard normalized relational model will be good for performance.

Trust us, you really don't want to go down the rabbit hole of creating a different table for each employee.
Was This Post Helpful? 0
  • +
  • -

#10 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4479
  • View blog
  • Posts: 7,801
  • Joined: 08-June 10

Re: .sdf tables question

Posted 19 December 2012 - 12:06 PM

I actually have another question: in your KPI table, will each column of each row be filled? Because if you need to track individual KPIs, there's a better way than making them each a column. You make a KPI table that defines what each KPI is, with an ID. Then you make an EmployeeKPI table that would look like this:


EmployeeID | FirstName | LastName 
-----------+-----------+-----------
 101110    | Curtis    | Rutland

====================================================

KpiId | KpiName 
------+--------------
 1    | Sales Target
 2    | LOC Committed

====================================================

EmployeeId | KpiId | KpiValue | Created
-----------+-------+----------+--------------------
 101110    | 2     | .75      | 2012-12-19 13:04:05



And you could write pivot reports that would come up with all the KPIs for a time frame for an Employee.
Was This Post Helpful? 0
  • +
  • -

#11 flexin1981  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 11-September 12

Re: .sdf tables question

Posted 19 December 2012 - 01:43 PM

Thanks Guys,

Curtis yes for each employee all columns for a given row would have been filled.

I like the way you have that setup, so i would have three tables Employee Records, KPI definitions & KPI tracking info right.

This makes more sense to me setting it out this way.

For my personal education why is it more efficient this way. Every logic bone i have thinks of this as a hierarchical structure with each step logically separating the DB to give a smaller range to look through when querying.

I.E

DB
|
table
|
columns

each step making the data that needs to be sifted through smaller.

Thanks Again

Flexin
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1