Dynamic table structure-needed?

[CadMonkey]

Hi,
Mucho gracias if you pros can help me! I've only completed 1 year of a degree...

I have a many to many table relationship that I have resolved with another table- therefore I now have two one to many relationships, fairly standard stuff. Basically I need to list items under a schedule. So currently the tables may be like this:

1 Schedule record can have many item records.

This is no problem but the schedule MAY be split up into various sections. E.g.

1 Schedule record can have many sections.
1 Section can have many items.

Okay but what if I want to store items either under a section or straight under the schedule?

1 Schedule record can have many sections.
1 Schedule can have many items.
1 Section can have many items. (Easy way out have a section that's PK'd to represent the Schedule)

Now the tricky bit.. where I need help really from a pro dude/dudess.

Can I have a set of relationships where the user can create sections and sub sections of those sections all within a schedule, and can then store items under the sechule itself, under a section, under a subsection of a section, under a subection of a subsection of a section? I.e. infinite nests? Sounds crazy- maybe it is. But this is a real life situ!

Many thanks,

Simon

 

[Len Boorman]

You can have basically whatever you want.

You had many to many relationship that you resolved quite correctly.

What you need to do is to firstly complete a conceptual diagram where you purely show the relationships be they M:N or 1:M. The important point here is to show all relationships between the entities you have identified.

When you have done this then you resolve M:N relationships

So the answer to your question is

Define what is required
Then decompose M:N relationships to 1:M relationships

HTH

Len

 

[CadMonkey]

Resolved?!

Well, thankyou.. I've tried but I think this one is sending me doolaly. Can anyone glean my problem from this?

(Please see attached erd pic)

 

[Len Boorman]

Clear the mind and go to beginning

You cannot have a data structure that grows at the whim of a user. You can have a data structure that allows for sub sets but you need to define how many subsets. Think of it in the way of the Structure of an item

A Car

Car is top item
consists of Engine
Body
Gearbox
Etc

Each of these consists of bits and those bits consist of bits etc which is your problem.

Well the only way to resolve is to say
You can have as many layers as you like providing it is not more than X. You need to decide X

I have done this with structures but good reasons for going to lower levels because with each layer the complexity doubles at least.

But its fun


So dynamic structure.......No

Len B

edit add

Okay so you could build in some very fancy chunk of code that added a table of known definition and referred to a data table to decide its name and what referential links should be but you would be building yourself a bit of a nightmare in my opinion.

L

 

[CadMonkey]

Thankyou for your help. Instead I think I may take this route:

To just have the simple saving items under a schedule format, however...

Create a new table realted to the schedule called sections:

1 schedule can have many sections:

Sections table:

ID... number
Schedule ID .... Num
Heading.... Text

Then in the table that stores the items to the schedules, i.e. the resolved schedule-items table each item has a section ID that is normally 0. (0 = attached to schedule) and will be listed under the schedule, (You can also assign the item to the ID of the heading.... (1 = first section etc) catch my drift.

You can then assign sections as children of each other by adding a bit of fancy code that assigns the section as a child of the one above it if there are no items stored under the above section. Apply a bit of conditional formatting... And bob's your uncle.

Thankyou for bouncing ideas off- very helpful.

Many thanks,

Simon

 

[Mile-O]

If I'm thinking right - late in the day now - you want a table that has a one to many with itself to manage sections in one table being a subsection of a section in the same table.

 

[Len Boorman]

Bouncing ideas is the way to develop the solution cos only you have a full grasp of your requirements but sometimes we all get a bit tunnel visioned

Len B

 

[Pat Hartman]

It looks like a hierarchial relationship -
Schedule-->Section-->Item
with the Section part being multi-leveled.

You can add a field called ParentSectionID to the section table. This will allow you to make the Section table n levels. Each section has a ParentSectionID except the top level in the hierarchy. You create a single relationship between two instances of the Section table in order to enforce RI between ParentSectionID and SectionID. Take a look at geneology databases or Bill of Material (BOM) databases to see how this type of relationship works.

If you know the maximum number of levels of Section, you can create queries that traverse the hierarchy. If not, you would need to write a recursive VBA sub to traverse the hierarchy.

 

[CadMonkey]

What quality replies! Sorry I haven't been in touch 'till now but I have multi Access projects looming. So do you pros make a living out of DB design- Pat and Mile?

I'm looking at your sample databases to see if I can get my head around this relationship. I almost wish I stayed on my degree......

Cheers

 

[Mile-O]

So do you pros make a living out of DB design- Pat and Mile?

Pat does, I know that.

As for me, I have no computer related qualification. I taught myself about 2-3 years ago.

With respect to the programming side - it's been a hobby since I was four so it was no great hardship making a jump for linear BASIC on an AMSTRAD CPC 464

10 Rem MyFirst Program
20 Rem by Mile-O-Phile
100 PRINT "What is your name?"
110 INPUT a$
120 PRINT "HELLO " & a$
130 END

to event driven code in VB.

 

[CadMonkey]

Ah I C! I started in qbasic when I was 10 and followed a long winding road to becoming a CAD techician who just writes DBs for employers....

I'm wandering if there are any examples of this one to many within itself relationship? I've searched the posts and sample DBs. I'll give it a whirl anyway and see if I can work with it.

A recursive code would be needed to write reports etc. I think this will make for interesting form designs. I am actually trying to show this hierarchy in a control where you can add and delete children/parents etc (sounds violent!) .... something like that.... seems like a complicated control to implement. Anyone any ideas or shall I take this post to the forms forum!!!

If I get a nice little streamlined number I'll post this baby up in the samples section, if anyone's interested.

Cheers

 

[Mile-O]

What version of Access are you using and I'll se if I can demonstrate this with the TreeView Control.

 

[CadMonkey]

Thankyou! I'm using Access 2000 and treeview control version 5.0 (Sp2) I've had a look at an example, but to be honest my understanding was limited. I wander if the ability to add a record (or node) from within the control is possible?

 

[Mile-O]

If you add the table which supplies the TreeView Control with its data then you can and remove - you just need to rerun the sub that creates its structure.

It's not an object I use but I'm sure you can add, delete, and rearrange with it.

 

[CadMonkey]

Thanks to your help Mile I can begin to implement this. I do wander if this one to many relationship within a table can be set up in access or shown in the relationships diagram, i'm very curious as to that the realtionship would look like!

Thanks again- hey do you get paid for this??

You should!

 

[Pat Hartman]

To create the relationship, you just add the table to the grid twice. Access will suffix each subsequent instance of a table with an underscore followed by a number so the first "duplicate" instance is labeled tblYourTable_1. You then draw the appropriate join lines and enforce RI and Cascade Delete. Here's a db that uses a self referencing table. It only goes one level though so it is not complex at all.
Code Table Maintenance Sample

 

[Mile-O]

do you get paid for this?

No, I don't. Like everyone else, it's voluntary.

 

[Len Boorman]

Just like to say to Pat and Mile. Well I certainly learnt from this as well. I use A97 and have had a couple of situations where I have had to use a recursive relationship. I have limited this recursion to 7 on both occassions. This has proved to certailnly be sufficient but it is very interseting to learn that a dynamic recursive situation is possible,

Always leaning something

Len B

 

[CadMonkey]

Columbo would say... "Just one more thing"... and solve it! Likewise:

To create the relationship, you just add the table to the grid twice... You then draw the appropriate join lines and enforce RI and Cascade Delete.

Hi Pat (or anyone who may answer!)

Thanks for the tip, although I do have a problem with the appropiate lines bit. I am having sever trouble with the ref. integrity part when I try to do this. To illustrate- this is the recursive table and it's realtionship with itself:

tblHeadings
Section_ID (one)-------|
NodeText
Parent_ID (many)<----|
Schedule_ID

I'm just trying to get the join lines correct in the Relationships Window

 

[Mile-O]

You should have:

tblHeadings and tblHeadings_1