Hi

I have a design issue, that I could use some input on.

I have a table (construction budget) linked to my main table (main budget)

I also have 3 other tables (sub budget a,b and c) - they are related to main table (main budget) and table (construction budget)

I can link a sub budget to a construction budget and a main budget - this is working fine with the relations.
My problem is when I access construction budgets.

I want to filter the list depending on what the item belongs to (sub budget a,b, and c). So I should have some sort of relation back from construction budget to sub budget a, b and c right?

I would prefer to have a drop down where I could select what table the item was linked to (sub budget a,b and c) but I don't know how to accomplish this. Any suggestions?

Thanks

Hi @dyvel,

Could you provide a bit more detail as to what fields/data you wish to store in each table?

Do the sub-budget tables share a similar data structure with oneanother? What I mean is, could you use the same fields/data structure and programmatically change the labels in your UI according to a flag you could set from a dropdown or similar (a, b or c)? Then you could add the FK fields to your main and construction budget tables...

Of course, this will only work if you have the same number of fields & datatypes.

Best wishes,

Gez

BTW, if that is an option for you or not, you'll be able to filter/group the values from sub tables based on your FKs for your select list quite simply using the ajax option in your forms.

Hi

My 3 sub tables does not contain the same type or amount of fields (different structure)

In that case, the easiest way (especially if you're not necessarily going to have a construction budget table for every record in your main table):
2 Foreign Keys on each of your sub tables - 1 to your main and another to your construction table

On the other hand, - ideally, to conform to the strictest DB standards - you'd only have 1 FK per table, and therefore your sub tables would FK to your construction budget. Your construction budget then needs an FK to your main. That way, you can traverse the tables from any direction and get to any parent/child table in one query. This is the best way to conform to the standards and maintain a normalised database.

Hope this helps!

Gez

This could be a good example to use the nested table system. Coming in future.

... Beiing able to connect any kind of sub-budget in your example. Treated always as a 'budget'.

audibleid : K+1

Great thanks!

I know you have your hands tied at the moment beta testing v2.0 but when do you think that this nested feature will emerge?

Many thanks,

Gez

audibleid wrote: In that case, the easiest way (especially if you're not necessarily going to have a construction budget table for every record in your main table):
2 Foreign Keys on each of your sub tables - 1 to your main and another to your construction table

On the other hand, - ideally, to conform to the strictest DB standards - you'd only have 1 FK per table, and therefore your sub tables would FK to your construction budget. Your construction budget then needs an FK to your main. That way, you can traverse the tables from any direction and get to any parent/child table in one query. This is the best way to conform to the standards and maintain a normalised database.

Hope this helps!
Gez

Thank you for your suggestions. Right now I'm using 2 foreign keys on each sub table, as I don't require a construction budget in every case.

If your sub tables aren't necessarily linked to your construction budget then you might consider linking them on FK to your main table only. You could then have a 'type' field on your sub tables - a dropdown or boolean for example - to set whether it is relating to a construction or main record.

I guess then you'd need to write a short function to check inside your save/update processes for your sub tables, whether in fact there is a record corresponding with your main table (foreign key) and the type you've set if its a construction record.

Hope this helps!

Gez