Am I heading for trouble? I've never had so many one-to-one before...

[ST4RCUTTER]

I have been asked to redesign a database that tracks a huge number of data points. These are projects and the original table had over 100 fields. I have managed to separate them to related tables in an attempt at normalization. They are:

tbl_workorders (main project info)
tbl_services (services ordered)
tbl_contacts (internal company contacts)
tbl_customers (customer information)
tbl_project_dates (milestones of project)

Now this is different from other databases I've designed because all of the tables need a one-to-one relationship with the main table (tbl_workorders).

Am I heading for trouble with so many tables existing in a one-to-one? Also, The table tbl_workorders has its primary key as an autonumber. I want any new order on this to create matching foreign keys on all the other tables...I assume this should be handled since I have enforced referential integrity with cascade on update/delete for all the other tables.

Thanks for your feedback!

 

[ST4RCUTTER]

Ok, well pressing onward! I have created a form from the main table and subforms for all the other tables.

I then added each subform to the main form...establishing the parent child links between each form using the PK of the main table and the FK found on each of the other tables.

Now, when I add a record to the main form, I see that each of the FK fields on the subforms are updated with the PK value on the mainform.

Situation: Each of the other tables have a PK that is set to autonumber.

Problem: None of the other tables are creating records to match the record on the main table! Even though I see the PK value on the subforms in the corresponding FK field, the record and the auto number are not being created on the related tables.

Any ideas? Anytime you add data to a table with an autonumber it should create a autonumber for that row...I don't get that. Thanks!

 

[ST4RCUTTER]

Ha...I realize I'm posting to myself for the most part, but I wanted to add that I might not have a problem here after all. Here is my thinking:

Initially my thought was every record created on the main table must generate a record on the child tables at the point of creation. This is really unnecessary though. Someone could update a field on one of the child tables weeks after the point of creation and the correct PK to FK would still exist.


Please disregard my 2nd post on this topic! I see now that this is really like the "sparse parent-to-child relationships" I've read about in other posts.

 

[neileg]

I agree with Pat. You need to read up on normalisation. Instead of 100 colums you should probably have 100 rows.

 

[wazz]

i think you've answered this for yourself but i'm not sure...
you don't necessarily need a PK (autonum) field in the related tables, just the FK (long int) to match the PK. for example, adding multiple phone numbers or email addresses to someone (this can look like 1:1 but is still 1:N). also, see previous posts.

 

[ST4RCUTTER]

Ok guys. I want to thank you for the feedback here. I went back and re-examined how I was arranging my tables. I had an epiphany and moved multiple date columns into one lookup column. I applied similar logic to all tables in an attempt to make them "narrow and deep". Now comes the tough part:

I am struggling here with the relationships because no other relationship examples in Northwind even come close to matching this. My concern here is that I am storing the work order number on every table so that is major repetition. I can't think of another way to effectively relate the tables in the manner they need to be represented here however. Can someone please review the relationships of this database? Perhaps you can see something I am not.


I have added my zipped database to this post so you can view the relationships or add a quick form with parent/child forms then repost. Thanks again!

 

[ST4RCUTTER]

Dang...try that attachment again

 

[ST4RCUTTER]

Would someone mind looking at my database and commenting? I am curious as to if I am headed in the right direction. I am storing the WO field in all the tables to use as a FK in all the tables and as the PK in the WO table...am I storing the Workorder # too many times? I know redundant data is bad, but does that apply for FK's on the many side of a relationship?

 

[stopher]

It’s difficult for me to comment as I don’t really know what a WorkOrder is in your context. Therefore I can’t really say whether your relationships are right or not.

The relationship between WorkOrder and contacts seems fine. I assume that the contacts are not a finite set of people ? If they are then you might consider a table containing the list of possible contacts (so that you don’t repeat all the contact detail). If not then what you have is fine.

Again with Costs you are saying that there will be many costs associated with each work order and your implementation looks fine. I would note though that there is nothing in the cost table to really distinguish each cost other than the CostID which is autonumber. So if some auditor wants to see how a physical piece of paper (maybe an invoice) relates to a cost line in your database then you need something to reference e.g. the invoice number

I don’t understand the Locations thing. You are saying that there are many locations associated with each WorkOrder. If this is true then fine. Also, as with contacts, are your locations repeated in more than one workorder. If so, then they deserve their own table (so that you don’t repeat all the detail for a specific location). In this case you would have a many-to-many to resolve.

TIDS I don’t understand. You have CircuitID in this table but also in the WorkOrder table. What is a TIDS and how many TIDS are associated with each WorkOrder?

Dates? You are saying that many dates are associated with each WorkOrder. But a list of dates is just meaningless. Do you mean “work carried out”? In which case your table should be called tbl_WorkDone or something like that so it actually means something i.e. Work was done on these dates.

I really don’t know enough about foc_history and service to add any more. But just convince yourself that they really are one-to-many.

I suspect there is still some work to do here but a more detailed explanation of your model is needed if you want more feedback. There could well be at least one many-to-many that needs resolving (but I might be wrong)

Hth
Chris

 

[wazz]

heading in the wrong direction. most of the relationships you've created are in the opposite direction. you do not need all of those wo fields. (also don't use the number sign in your field names (#). use Num if you must).

you must (!) read up on normalization.

i haven't looked at these for a while so they might not be perfect but start here, at least.
http://office.microsoft.com/en-ca/access/HP051884441033.aspx
http://support.microsoft.com/default.aspx?scid=kb;en-us;283698
http://www.datamodel.org/NormalizationRules.html
http://r937.com/relational.html

 

[ST4RCUTTER]

Thanks for all the feedback guys. I could tell something was wrong here. Perhaps it would be helpful if I describe what it is I'm tracking a little more clearly.

These are projects...more specifically commercial install projects. They have various elements to track:

1. Internal contacts working on the project (multiple names that fit within 7 general categories such as Project manger, project engineer, Transport engineer etc)
   [*]Costs (breaks down into 5 possible types with amounts for each)
   [*]Dates (as many as 33 date milestones per project but only one date type per project)
   [*]Locations (each project has 2 possible locations called an A and Z location)
   [*]Services (each project has multiple services associated it and detailed here)
   [*]TIDS (These are circuit ID's to be installed and there could be any number of them)
   [*]FOC (This stands for Firm Order Commitment - this is the date we guarantee service installation. This date could be revised to a later date so there are multiple possible FOC's for each project)
   [*]Workorders (This contains fields that are unique and non-repeating for each project such as customer name, type of project, and job status.)

But a list of dates is just meaningless.

There are lookup fields on the dates, locations, and costs tables to identify which type of item it is. As for dates, there should really only be one type of date per work order so right there I might need to find some way to limit the date types so each is only used once per work order.

The reality here is that all of these datapoints tracked in the other tables could exist as a one-to-one relationship with each workorder on the workorder table. That would result in one massive table however.