Need help with a table structure

[Billkamm]

I have 5 tables in a productivity database: Groups, Task, Sub-Task, Market, and Product

A combination of any field in those 5 tables can be an entry into the productivity database. So like Group1, Task2, Sub-Task4, Market1, and Product2 could be an entry.

Also, a lot of them don't need the lower categories such as sub-task, market, and product. Those can be left NULL.

For each "entry" I need to specific other attributes that go along with each entry. I past productivity databases I combined all of my groups into a central table (called tblProcess in the last database)

Then I would have:
pkProcessId, Group, Task, Sub-Task, etc.. Attribute1, Attribute2, etc.. as the field names in tblProcess.

However, I'm sure that this is a terrible design and I would like to improve upon in my new database. Does anyone have any suggestions or is this the best way of going about things?

 

[lespaul]

Have you read the document linked below? That's a standard first reading for database designing.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[stix4t2]

Have you considered using an index table to associate the parts that need to be together. One idx ID for each of the five tables and a unique ID for each listing. Then the attribute table could link attributes to the unique ID.

 

[Billkamm]

stix4t2 that is exactly what I'm doing now. I'm trying to find a better way, because that doesn't feel right to me. It may be the right way, but it just doesn't feel right to me.

 

[stix4t2]

Ok, then we should visit other aspects that are creating this uneasiness. Do other associations exist that you have yet explained. Like are products specific to markets, so by indexing product, there is no need to specify market because the association is in another index.

 

[Billkamm]

Table names are in bold, this is the basic structure I am using now (with some non-related fields removed)

Groups
pkGroupId
GroupName

Tasks
pkTaskId
TaskName

SubTasks
pkSubTaskId
SubTaskName

Markets
pkMarketId
MarketName

Products
pkProductId
ProductName

Processes
pkProcessId
fkGroupId
fkTaskId
fkSub-TaskId
fkMarketId
fkProductId


Now any combination of Groups, Tasks, Sub-Tasks, Markets, and Products is possible.

 

[lespaul]

does the sub-tasks table have different fields from the Tasks? I would assume that all are tasks, only some are sub-tasks (a hierarchal relation).

Could you please repost your table structure with the non-related fields included? It helps to determine normalization if you list all the fields.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[stix4t2]

Well I know what you mean about it not seeming right. Maybe we should look at the business logic, because I bet you it would hold the key. For instance how do you prioritize the levels among the 5 tables. Is it Group market product task subtask, or is it market product group task subtask. If we look at this we might find better ways to break it down. Also consider a general input structure and what suits best for it. i.e. select product, edit task and sub task. And select market and select products. Kind of makes a difference.

 

[Billkamm]

It is a hierarchy. Each Group contains Tasks which contain Sub-Tasks which fall into a specific market for a specific product.

lespaul: those are the exact fields. I removed the other ones as they weren't needed.

 

[lespaul]

you mean you have no other fields in ANY of your tables?

 

[stix4t2]

Bill,

What we are looking for here is normalization. Which fields have duplicate values in different rows. So in your description the product is variable based on the group, task, sub-task and market?

Lets look a little deeper. As groups change, will they use the same task/sub-task? Will markets have the same products? What can you tell me about this logic?

 

[Billkamm]

stix4t2: It is setup so any group can have any task which can have any sub-task. Product and Market can be associated with any of those processes, but don't have to be.

For example you can a process

Group 1 --> Office Task 1 --> Filing
--> Sorting
--> Printing
--> Emailing

Group 1 --> Office Task 2 --> Filing
--> Some Sub-Task

Group 2 --> Office Task 1

Group 2 --> Office Task 2 --> Filing
--> Faxing

Group 2 --> Office Task 2 --> Emailing --> Product 1
--> Product 2


That is about how the list of data looks.


lespaul here are the other related tables (assume there is a unique primary key with each of these):

Task Entries
Process
Quantity
Associated Time As of Time Of Entry
fkEmployee
fkEmployee’s Manager At Time Of Entry
fkEmployee’s Access Level At Time Of Entry
Time Of Entry

Process Time
fkProcessId
Associated Time

Process Definitions
fkProcessId
fkFree Form Field Type
fkProcess Type
Non-Productive Task (yes/no)

Free Form Field Types
Type

Process Type
Type