Unable to add additonal relationship

[lisarmj]

I have a large database (many tables) and all are joined to one main table with a 1-1 relationship.

When trying to join a table I received an error msg that there are too many indexes on the main table and to delete some. Of course I can't do that...any suggestions?

 

[boblarson]

Actually, you potentially can. It sounds like your design may be somewhat flawed. Can you post a screenshot of the relationships and maybe a screenshot of your main table in design mode?

 

[lisarmj]

I have attached a screen print of the database relationships as well as the design of the main table (tblJobInfo).

I am tracking engineering projects where tblJobInfo stores the basics (job number, project manager etc) - for each project there can be up to five components (water, wastewater, storm water, site planning and buildings) - for each component there can be upwards of 200 fields (each a yes/no value).

My original design plan has one main table (tblJobInfo) then a table for each category of each component (ie: the Water component has 10 categories each with 5 - 30 fields).

Since each component can only occur for each project one time, I have created 1-1 relationships with the main table. I suppose I could make one giant table for each component, but to me that seems cumbersome especially when creating the forms and search functions.

All of the tables are stored in a back-end database and all the forms/queries/code etc are in the front-end.

Any advice is appreciated.

Thanks!
Lisa

 

[boblarson]

Sorry, but the screenshots are not at a good resolution. I can't read anything on it and when I save it and zoom, it is still unreadable.

 

[lisarmj]

Thank you for your patience - I hope these are of better quality.

--Lisa

 

[boblarson]

The tblJobInfo one came out okay, but the rel one didn't. And that appears to be an important one we need to see.

 

[lisarmj]

Here we go again! - Hopefully, this will work.

Thanks again,
Lisa

 

[boblarson]

MUCH BETTER!!! I'll take a look and get back to you.

 

[boblarson]

Okay, yes, your database is not designed properly. As Simon Cowell, from American Idol, would say, "I'm not trying to be rude but,..." you really need to revisit the design. There's just too much to try to list at this point.

We, at the forum here, can assist you in that but first we'll need some more information on what, exactly, you are trying to track. Also, if you can post your database it might help as we can see what kind of fields you have currently and can ask better questions about them.

So, you desperately need to take care of the design here first and then the rest should fall into place.

 

[EdFred]

Oh man, I am never, ever, ever posting my relationship screen shot.

 

[boblarson]

Oh man, I am never, ever, ever posting my relationship screen shot.

We'll get to you somehow

 

[EdFred]

We'll get to you somehow

Let's just say that I have no fewer than 20 tables that are on the the many side of at least two 1-to-many relationships. Oh, and I have spaces in field names, and my table names don't start with tbl and in order to view it you need a screen resolution of at least 2600 pixels wide.

 

[lisarmj]

Ok - here goes...all of the data is bogus at this point...just entered in for testing purposes.

I have already distributed the database for use with just the Water component - it is running nicely, however of course that is useless if I can't build the remaining pieces.

The reason I mention this is that any redesign will mean importing data from the original into the new.

I am open to all suggestions. Thanks again for looking at this...if the front-end is needed as well please let me know.

Thanks!
Lisa

 

[lisarmj]

I was going to add the front-end as an attachment because I think it would help understand the objective of the database. However, even zipped it is too large to upload...is there any way around this?

Lisa

 

[boblarson]

I can PM you my email address. Because of the complexity of the relationship issue, it may take a while to get the time to work on it. I will, but I just wanted to warn you that you may not hear anything for a few days.

 

[lisarmj]

That's fine...again I appreciate your assistance - I understand that your time is valuable and anything you can offer will be useful.

--Lisa

 

[boblarson]

Due to the extreme number of tables that you have, I don't necessarily think you are going to be able to set Referential Integrity on all of them. I am not sure how to get you the ability to add other tables to your relationships, but the Cascade Updates/Deletes definitely are not going to be able to be set for all, so I think I would eliminate them as that will allow you to at least set the Referential Integrity (I think) for all of them.

Also, here's a few tips:
1. Your relationships should be set in the backend and NONE in the front end as the backend is where the tables lie – the front end only links.
2. Your ID fields are not set up properly. Your tables should each have a distinct primary key (don’t name them all fldID). The Job table should have a primary key as well, but JobID will be the FOREIGN key in the other tables.
3. Your tblClients should have a numeric key (autonumber) instead of using the text as the primary key.
4. Your JobInfo table should not be storing the text name of the client, but the primary key in the tblClients table.

Also, your project manager also should be stored in the job info table with the id number and not the initials. I'm going to post the backend again with some changes (I didn't implement everything as I have run out of time available) Just kind of take a look at how the Id's should be setup

 

[lisarmj]

Bob

Thank you for the time you have put into this. I have implemented the changes you suggested. In addition, though I had created so many tables for the sake of ease, I have combined all of the major categories together (i.e. 15 Stormwater tables are now 1 large table). Since they are true 1-1 relationships 1 large table works as well as 15 smaller ones. I originally didn't want to deal with a table with so many fields but it has turned out not to be a difficult as I had thought. There have been some challenges in updating form design and I'm sure there will be a few more with importing current data into the new model, but all worth it.
Thanks again!
Lisa

 

[boblarson]

Glad to hear that you're making progress.