please help - database design [modified]
-
Hello, I'd appreciate if someone could kindly help me with the following database design issue. I've already solved it, but my solution is not much elegant. I'm working on this SQL database for one of my friends. It will make her work more efficient since she's currently dealing with a tangled up set of excel tables. The database will hold various data for several thousand employees. This would be a piece of cake, but we also need to track the changes of those data values in time. For example, we need to know that John Smith was in Quality Department in October, but since November, he's been in Services Department, etc. Having a separate "historical data states" table for each employee (or, alternatively, for each data type) is not a good idea, so I designed a simple table named "TimeTableItem" that stores: - a link to the table that holds table.column names (for instance "19" for dbo.Department.DepartmentID table) to which the current row relates ("TimeTableDataTypeID" column below) - and an ID of the related value itself (for example, "7" for Services Department within the Department table - "TimeTableDataValueID" column below) The actual table design: - TimeTableItemID (PK, int, not null) - PersonID (FK, int, not null) - TimeTableDataTypeID (int, not null) - TimeTableDataValueID (int, not null) - StartTime (dateteime, null) (null is treated as "from the beginning of time" :) - EndTime (datetime, null) (null is treated as "forever" :) - CreatedOn (datetime, null) - ModifiedOn (datetime, null) Obviously, the PersonID is a link to the Person table. By the same token, StartTime and EndTime define in which time span the data for the particular person is in effect, CreatedOn and ModifiedOn denotes these self-explanatory timestamps. The trouble with this kind of design is that we don't know beforehand to WHAT employee data (Department? Position? Salary band?) will the next row be related, so we can't set a relation between the tables here (we dont' know what will the other table be). This means that there's no way to set a foreign key constraint on a column that holds the VALUE of the related data element. This is not much consistent and might lead to a situation when my friend deletes a department from the departments table that is used somewhere else. This will result in orphaned rows. Is there any way to solve this efficiently? Thank you for any input, Michal -- modified at 21:14 Monday 4th December, 2006
-
Hello, I'd appreciate if someone could kindly help me with the following database design issue. I've already solved it, but my solution is not much elegant. I'm working on this SQL database for one of my friends. It will make her work more efficient since she's currently dealing with a tangled up set of excel tables. The database will hold various data for several thousand employees. This would be a piece of cake, but we also need to track the changes of those data values in time. For example, we need to know that John Smith was in Quality Department in October, but since November, he's been in Services Department, etc. Having a separate "historical data states" table for each employee (or, alternatively, for each data type) is not a good idea, so I designed a simple table named "TimeTableItem" that stores: - a link to the table that holds table.column names (for instance "19" for dbo.Department.DepartmentID table) to which the current row relates ("TimeTableDataTypeID" column below) - and an ID of the related value itself (for example, "7" for Services Department within the Department table - "TimeTableDataValueID" column below) The actual table design: - TimeTableItemID (PK, int, not null) - PersonID (FK, int, not null) - TimeTableDataTypeID (int, not null) - TimeTableDataValueID (int, not null) - StartTime (dateteime, null) (null is treated as "from the beginning of time" :) - EndTime (datetime, null) (null is treated as "forever" :) - CreatedOn (datetime, null) - ModifiedOn (datetime, null) Obviously, the PersonID is a link to the Person table. By the same token, StartTime and EndTime define in which time span the data for the particular person is in effect, CreatedOn and ModifiedOn denotes these self-explanatory timestamps. The trouble with this kind of design is that we don't know beforehand to WHAT employee data (Department? Position? Salary band?) will the next row be related, so we can't set a relation between the tables here (we dont' know what will the other table be). This means that there's no way to set a foreign key constraint on a column that holds the VALUE of the related data element. This is not much consistent and might lead to a situation when my friend deletes a department from the departments table that is used somewhere else. This will result in orphaned rows. Is there any way to solve this efficiently? Thank you for any input, Michal -- modified at 21:14 Monday 4th December, 2006
michal.kreslik wrote:
The trouble with this kind of design is that we don't know beforehand to WHAT employee data (Department? Position? Salary band?)
Then use a table for each type. A HistoricalDepartment table, a HistoricalSalary table and so on.
Upcoming Scottish Developers events: * We are starting a series of events in Glasgow in 2007. Are you interested in a particular subject, or as a speaker? * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog | Photos
-
michal.kreslik wrote:
The trouble with this kind of design is that we don't know beforehand to WHAT employee data (Department? Position? Salary band?)
Then use a table for each type. A HistoricalDepartment table, a HistoricalSalary table and so on.
Upcoming Scottish Developers events: * We are starting a series of events in Glasgow in 2007. Are you interested in a particular subject, or as a speaker? * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog | Photos
Yes, that's one of the options I was talking about, but is that really a systematical solution to implement a distinct table for each data type? I don't think so. There must be a way to "join" the individual datatype tables' values' relations in one master table. Thanks, Michal