database question..

[lendarios]

Originally Posted by Grooverider

Yes that is actually superior to my current set up. The queries will be extremely complicated though.. ugh. :

I may adopt this set up actually, to make the queries rapid when the database becomes really huge.

I have a separate, smaller but similar project to trial first as a proof of concept

Printing your design out now..

I use Sql Server Management studio for my daily work. Now after you place the proper foreign keys in the tables 4,5 and 6, you can use the Create View to visually create the query. Just drag and drop the tables. Even if you are not using it as your database solution i would use it just to create the queries visually.

The key to make the search tiger fast is the placing of Foreign keys in tables 4,5,6 to ensure data integrity and unclustered indexes on those tables on each of those columns. Now also to prevent the duplicate relations, you can place a unique index in the combination of [column a - column B] on tables 4 5 6.