If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Northwind
I want to modify the Northwind db example.
Looking at the design, I want to include another 3 or 4 tables that follow the schema of the "Order Details". If I look at the relationship between "Orders" and "Order Details" I think I want to be able to create multiple 1:M tables. For instance, I may have a "Project" that has multiple "workers", "multiple costs", "multiple skill requirements". All of these "multiples" I want to add in datasheet view for the particular project. Any suggestions how modity the Northwind tables and relationships to accomodate this? Thanks in advance, Mike |
#2
|
|||
|
|||
Northwind
If Northwind is close enough to what you want, then that's
fine. You can make a copy of the database (so as to keep Northwind handy as is for a reference), and then make changes to the copy. One-to-many relationships is what you want to have 99% of the time, with the rest of them being 1-to-1s. Your project will have workers. Those workers will have costs (I'm presuming?), skills, plus possibly a host of other data you want to capture about them. In your project table, your project number or ID will be unique, and therefore your primary key. You should also have a worker table with all the worker data included. If there's only a few skills they can possess, you might make a few checkboxes (Yes/No fields) right in the worker table. Or you could have a combo box in several different fields in the worker table, with the different skills in the lookup table for each combo box. The problem with either approach above is limiting how many skills a worker can have by the number of fields you include for that in the worker table. If you find yourself needing lots of skills and the capacity for lots of skills per worker, you could: 1) Separate the skills out into their own table, using the employee ID for the primary key and creating yes/no fields for each skill. (This is a time when a one-to-one relationship is a good thing - when the sheer number of fields for one record can be ginormous.) Or, 2) Make a skills table. Modify the sales invoice to have it pull info from the project table, the worker table, and the skills table. This would make one form that would give you all the information on a project, including workers and skills. (Your project info would be the body of the "invoice", the different workers the subform of "line items", and the skills another subform of "line items" you'd have to add.) This means populating a new table with info from the others to associate all this info together, though, so you may not want to do that. (You *would* if this was really a sales invoice, so you could later go back and see what was sold on each invoice.) That's probably enough to hit you with at once. -----Original Message----- I want to modify the Northwind db example. Looking at the design, I want to include another 3 or 4 tables that follow the schema of the "Order Details". If I look at the relationship between "Orders" and "Order Details" I think I want to be able to create multiple 1:M tables. For instance, I may have a "Project" that has multiple "workers", "multiple costs", "multiple skill requirements". All of these "multiples" I want to add in datasheet view for the particular project. Any suggestions how modity the Northwind tables and relationships to accomodate this? Thanks in advance, Mike |
#3
|
|||
|
|||
Northwind
Rose:
Thank you so much for your detailed response. Having read what you suggested, I doubt that modifying the Northwind db is actually the best way what I'm trying to achieve. Essentially, here's the problem I'm having... I have multiple tables that are all closely related to one another (at least as far as I'm concerned). The concept of Northwind is really easily to follow. I mean it's very obvious to follow that Suppliers have Products are product are linked to Orders which in turn are placed by Customers. However, using a concept that involves the following tables is more difficult to understand: - Boards - Budget Categories - Concepts - Employees - Organizations - Projects - Skills - Tasks Thinking of these tables, I could see multiple Many-To-Many relationships amongst all of these tables. For instance, the following might help get better clarification on my thinking pattern. Boards: 1. Boards are subordinate to an Organization: 2. Boards have multile Employees 3. Running Boards will end up costing money, so there will be funds allocated via Budget Categories for each board 4. Board (well the members) will make decisions that result into tasking people w/ something. This "something" might lead into Projects Budget Categories: 1. Each budget category is - more than likely - part of multiple project 2. Budget Categories are utilized by various Organizations (actually Sub-Organizations) Concepts: 1. Concept might turn into Projects and then into Tasks 2. They have to be funded by multiple Budget Categories 3. They will be supported by multiple Employees; therefore, multiple skills are need to achieve a successful concept implementation Employees: 1. Many employees are part of many organizations (or boards, or projects) 2. Many employees need many skills 3. Many employees have many tasks Organizations: 1. will have many projects, boards, employees, tasks, budget categories, etc. Projects: 1. I'm repeating myself here... you get the drift ... Skills: 1. They are part of all (many) employee's backgrounds -- at least I hope so 8) 2. Many skills are required to running many projects, support many boards 3. Many skills will have to be subsidized by many budget categories (the more you know the more funds may have to be allocated from different sources) Finally, Tasks: 1. Many tasks are completed by many employees 2. They all have to be funded by many budget categories 3. They are part of many organizations Wow, as you might realize, this is quite different than "Northwind", isn't it? Looking at all of the "relationships" I'm not sure if I have ended up with a challenging project here. For me, it's seems very difficult to get a handle on this an create relationships that would make sense (in a most efficient database design) I'm sure you're busy but if there's any way you could provide me more feedback on this, I truly would appreciate it. Thanks in advance, Mike "Rose" wrote in message ... If Northwind is close enough to what you want, then that's fine. You can make a copy of the database (so as to keep Northwind handy as is for a reference), and then make changes to the copy. One-to-many relationships is what you want to have 99% of the time, with the rest of them being 1-to-1s. Your project will have workers. Those workers will have costs (I'm presuming?), skills, plus possibly a host of other data you want to capture about them. In your project table, your project number or ID will be unique, and therefore your primary key. You should also have a worker table with all the worker data included. If there's only a few skills they can possess, you might make a few checkboxes (Yes/No fields) right in the worker table. Or you could have a combo box in several different fields in the worker table, with the different skills in the lookup table for each combo box. The problem with either approach above is limiting how many skills a worker can have by the number of fields you include for that in the worker table. If you find yourself needing lots of skills and the capacity for lots of skills per worker, you could: 1) Separate the skills out into their own table, using the employee ID for the primary key and creating yes/no fields for each skill. (This is a time when a one-to-one relationship is a good thing - when the sheer number of fields for one record can be ginormous.) Or, 2) Make a skills table. Modify the sales invoice to have it pull info from the project table, the worker table, and the skills table. This would make one form that would give you all the information on a project, including workers and skills. (Your project info would be the body of the "invoice", the different workers the subform of "line items", and the skills another subform of "line items" you'd have to add.) This means populating a new table with info from the others to associate all this info together, though, so you may not want to do that. (You *would* if this was really a sales invoice, so you could later go back and see what was sold on each invoice.) That's probably enough to hit you with at once. -----Original Message----- I want to modify the Northwind db example. Looking at the design, I want to include another 3 or 4 tables that follow the schema of the "Order Details". If I look at the relationship between "Orders" and "Order Details" I think I want to be able to create multiple 1:M tables. For instance, I may have a "Project" that has multiple "workers", "multiple costs", "multiple skill requirements". All of these "multiples" I want to add in datasheet view for the particular project. Any suggestions how modity the Northwind tables and relationships to accomodate this? Thanks in advance, Mike |
#4
|
|||
|
|||
Northwind
First, it might be easier to use MS Project for at least
part of what you're doing. Project makes tracking tasks, the people working on those tasks, and how much time and money it's costing you very easy to do. But back to the example: If you find a many-to-many, it means there is a table in-between that needs to be created. For instance, to use the sales example again, you may think that you're going to be selling the same products to repeat customers, so where's the one side of a relationship? The answer is a sales invoice, which tracks each individual (read: unique) instance of a customer buying stuff. The sales invoice is in a 1-M relationship with both customers and products. To start with your circumstance, organizations. How much information do you need to capture just about organizations? If it's just a name, but no address or phone or head or anything like that, it can just be a field in your boards table. Otherwise, it'll be its own table, and will relate to boards when you include the org's primary key in the boards tables. Unique employees (primary key) - can they belong to more than one board? If so, you can either do multiple yes/no fields in Employees, or have a 3rd table that does nothing but track what employees are on what board and use an AN for your PM. (This would be akin to the line items on a sales invoice - you could have multiple lines, meaning the invoice number could repeat, as could the product, but you've got an AN being the PM.) A similar table will probably do for your costs - each cost is assigned to a board or project or whatever, and the table is just a list of costs. Probably your task assignments will work out similarly. So have your first tables be all your unique stuff - employees, boards, tasks and skills perhaps, etc. Then think of whether a straight relationship will work between them, or if you need a table like the above to bring their information together in 1 place to track it. The line items on a sales invoice is the easiest example of that. A line item table will capture the invoice number, the product being sold and its costs, plus the quantity of that product being ordered and its final line item cost (quantity X price). Since absolutely everything can repeat, an AN is required for a PM. It's usually displayed as a subform in the invoice main form, so you can see both tables at the same time, but they *are* 2 separate tables. Does that make sense? -----Original Message----- Rose: Thank you so much for your detailed response. Having read what you suggested, I doubt that modifying the Northwind db is actually the best way what I'm trying to achieve. Essentially, here's the problem I'm having... I have multiple tables that are all closely related to one another (at least as far as I'm concerned). The concept of Northwind is really easily to follow. I mean it's very obvious to follow that Suppliers have Products are product are linked to Orders which in turn are placed by Customers. However, using a concept that involves the following tables is more difficult to understand: - Boards - Budget Categories - Concepts - Employees - Organizations - Projects - Skills - Tasks Thinking of these tables, I could see multiple Many-To-Many relationships amongst all of these tables. For instance, the following might help get better clarification on my thinking pattern. Boards: 1. Boards are subordinate to an Organization: 2. Boards have multile Employees 3. Running Boards will end up costing money, so there will be funds allocated via Budget Categories for each board 4. Board (well the members) will make decisions that result into tasking people w/ something. This "something" might lead into Projects Budget Categories: 1. Each budget category is - more than likely - part of multiple project 2. Budget Categories are utilized by various Organizations (actually Sub-Organizations) Concepts: 1. Concept might turn into Projects and then into Tasks 2. They have to be funded by multiple Budget Categories 3. They will be supported by multiple Employees; therefore, multiple skills are need to achieve a successful concept implementation Employees: 1. Many employees are part of many organizations (or boards, or projects) 2. Many employees need many skills 3. Many employees have many tasks Organizations: 1. will have many projects, boards, employees, tasks, budget categories, etc. Projects: 1. I'm repeating myself here... you get the drift ... Skills: 1. They are part of all (many) employee's backgrounds -- at least I hope so 8) 2. Many skills are required to running many projects, support many boards 3. Many skills will have to be subsidized by many budget categories (the more you know the more funds may have to be allocated from different sources) Finally, Tasks: 1. Many tasks are completed by many employees 2. They all have to be funded by many budget categories 3. They are part of many organizations Wow, as you might realize, this is quite different than "Northwind", isn't it? Looking at all of the "relationships" I'm not sure if I have ended up with a challenging project here. For me, it's seems very difficult to get a handle on this an create relationships that would make sense (in a most efficient database design) I'm sure you're busy but if there's any way you could provide me more feedback on this, I truly would appreciate it. Thanks in advance, Mike "Rose" wrote in message ... If Northwind is close enough to what you want, then that's fine. You can make a copy of the database (so as to keep Northwind handy as is for a reference), and then make changes to the copy. One-to-many relationships is what you want to have 99% of the time, with the rest of them being 1-to-1s. Your project will have workers. Those workers will have costs (I'm presuming?), skills, plus possibly a host of other data you want to capture about them. In your project table, your project number or ID will be unique, and therefore your primary key. You should also have a worker table with all the worker data included. If there's only a few skills they can possess, you might make a few checkboxes (Yes/No fields) right in the worker table. Or you could have a combo box in several different fields in the worker table, with the different skills in the lookup table for each combo box. The problem with either approach above is limiting how many skills a worker can have by the number of fields you include for that in the worker table. If you find yourself needing lots of skills and the capacity for lots of skills per worker, you could: 1) Separate the skills out into their own table, using the employee ID for the primary key and creating yes/no fields for each skill. (This is a time when a one-to-one relationship is a good thing - when the sheer number of fields for one record can be ginormous.) Or, 2) Make a skills table. Modify the sales invoice to have it pull info from the project table, the worker table, and the skills table. This would make one form that would give you all the information on a project, including workers and skills. (Your project info would be the body of the "invoice", the different workers the subform of "line items", and the skills another subform of "line items" you'd have to add.) This means populating a new table with info from the others to associate all this info together, though, so you may not want to do that. (You *would* if this was really a sales invoice, so you could later go back and see what was sold on each invoice.) That's probably enough to hit you with at once. -----Original Message----- I want to modify the Northwind db example. Looking at the design, I want to include another 3 or 4 tables that follow the schema of the "Order Details". If I look at the relationship between "Orders" and "Order Details" I think I want to be able to create multiple 1:M tables. For instance, I may have a "Project" that has multiple "workers", "multiple costs", "multiple skill requirements". All of these "multiples" I want to add in datasheet view for the particular project. Any suggestions how modity the Northwind tables and relationships to accomodate this? Thanks in advance, Mike . |
Thread Tools | |
Display Modes | |
|
|