Hi Tim & Team,

Not sure if this is one to post under general.

Just wondering as far as ElevateDB is concerned, what you would consider
the most efficient way to store multiple selections in a table from
another table.

Let me quickly explain.

Say I have the following table:

StyleID    Desc
1             Testing description1
2             Testing description2
3             Testing description3
4             Testing description4

And the following table which I want to store multiple styles in.

ID     StyleID     name            age
1     1,2,3,4      Joe BLoggs     45
2     1,4           Fred BLoggs    23
3     1,3           Jim BLoggs      34


Would this be the best way to have the ID's seperate by something like a
comman in the styleID field and then split them at the time?

Thanks,

Phil


Correctly you should have a cross reference table along the lines of

ID    StyleID
1    1
1    2
1    3
1    4
2    1
2    4
3    1
3    3

But, I'm a heretic, and it really depends on what you want to do with the information. In a lot of areas rather than using a cross reference table I'm using the full text indexing capabilities of ElevateDB (and DBISAM before it)

If you can give a bit more info about how you intend to use the tables, how they will be processed and displayed we can probably give better advice.

Roy Lambert [Team Elevate]

> If you can give a bit more info about how you intend to use the tables, how they will be processed and displayed we can probably give better advice.
>
> Roy Lambert [Team Elevate]

Thanks Roy! Well the styles linked to the main table will be used in a
screen where multiple style selections will be allowed and also in
reports as well as, so I'm thinking the cross-reference table sounds
like an idea, that will make it easier and faster to use within reports
is that right?

Phil

>Thanks Roy! Well the styles linked to the main table will be used in a
>screen where multiple style selections will be allowed and also in
>reports as well as, so I'm thinking the cross-reference table sounds
>like an idea, that will make it easier and faster to use within reports
>is that right?

Possibly, but not necessarily. It depends on just what you're doing. To give you an example from my recruitment app.

I have

Contacts
Companies
Career
Skills

amongst others. Career is the link between companies and contacts, and is done that way so that its easy to get a list of contacts at a company as well as a list of companies a contact has worked at. Skills is a table holding the various skills that contacts may have (say Delphi and SQL), but there is no link table and for each contact the skills for that contact are held in a memo field, and there is a full text index for the field.

Whilst I want to go in either direction from career I only ever want to go in one direction with skills ie show me everyone with these skills. The only purpose in having the skills is to validate input.

Because there's a full text index access is fast and because there's link table sql and table maintenance is easier (eg delete a contact and their skills list is gone as well without having to delete from another table). I've used this approach for several fields in both the company and contact tables and it works very well.

Sorry to ramble on. A cross reference table should be a bit easier to program and use but there are other options.

Roy Lambert [Team Elevate]

Excellent thanks Roy. I see what you mean and really it's very similar
in my situation, so I think I'll just use a memo with a full text index
for the field.

Thanks again, good explanation

Sorry Roy, just want to confirm since we've talked memos in these
posts... Should I be using a CLOB in ElevateDB for storing multiple
styles (text content) as oppose to a BLOB?

Phil


Dealers choice really. I tend to use CLOBs for these sort of things simply so that I can see the contents in EDBManager. Back in DBISAM I used MEMO fields for everything but now if I'm stuffing a table or graphic in I use BLOBs.

I can't remember trying it, so I don't know if you can have a full text index on a BLOB.

Roy Lambert

<< I can't remember trying it, so I don't know if you can have a full
text index on a BLOB.

Roy Lambert >>

Yep BLOBS don't allow Text Index in the ElevateDB Manager I just tried
it in.

Thanks Roy