Could anyone advise me if I did it right or wrong, and if wrong, suggest me a better solution.
I have six main tables that go hierarchically one under another (like MAIN_1...MAIN_6). MAIN_6 has a FK that references MAIN_5, which has FK that references MAIN_4, and so on. There are a few tables that are alongside the main tables (like SIDE_1...SIDE_4) SIDE_1 and SIDE_2 both have FK that references MAIN_1, SIDE_3 has FK that references MAIN_3, and SIDE_4 has FK that references MAIN_6. All these I have set to Cascade Update and Cascade Delete.

Then I have twenty list tables (like LIST_01...LIST_20). These are serving just to contain a list items in them, and they are related to all MAIN and SIDE tables (where MAIN or SIDE tables have FK that reference LIST tables). Most of them are linked to MAIN_6, and I have four lists that are used in thirty related fields in MAIN_6 (10, 10, 5, and 5; like fields Option_01...Option_10 are referencing List_01_ID in LIST_01; Color_01...Color_05 are referencing List_03_ID in LIST_03; PercentageOption_01...PercentageOption_10 and PercentageColor_01...PercentageColor_05 are referencing List_01_ID in LIST_02). I set all the lists to Cascade Update, except the first three lists, since SQL was throwing errors at me (like, 'MAIN_01' table - Unable to create relationship 'FK_MAIN_01_LIST_01_Option_02'. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing FOREIGN KEY constraint 'FK_MAIN_01_LIST_01_Option_02' on table 'MAIN_01' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.)

The reasoning behind having lists is to keep the database from bloating (keep the size down) by using field IDs, which are ints, as relationship keys. In other words, if LIST_03 has entries of 'Black', 'White', 'Red', 'Blue', 'Green', with IDs of '1', '2', '3', '4', '5', and I select White, the entry in MAIN_6 for the related field will be '2' in each record (where it's selected) as oppose to 'White' repeating many times in various records.

Of course I scripted the whole table creation and primary key selection, then went to Enterprise Manager and created all the FKeys and saved the change script to the file so I could add it to the '01.00.00.SqlDataProvider' file.

Once I'm completely done with the scripting of tables and relationships, I should start working on scripting procedures, and then start working on a module code (in VB.Net and for DNN 4.0).

I'm trying to learn the module thing the hard way, but I figured if I can nail it down -- I will have learned alot. Could anyone, also, suggest any tutorials for building modules (DNN 4.0 specific based on .Net v2.0), which are walking you step by step through the process and explaining each step thoroughly. The best of course would be video tutorials, but all I can find are for DNN 3.0, which is based on .Net v1.1.

I'm working with VS2005.

Thanks in advance,

Waldis

Thanks, Erik! I did go through that post, and it's a great one, but I need something that's beyond one page form. There is another one he did for DNN 4.3 or higher: http://www.adefwebserver.com/DotNetNukeHELP/DNN_Things4Sale/

I will need to have like six or more forms that link together forcing a user to go in a certain order without skipping the fields that are required; then I will need to have some admin side forms, like entering the values for lists and such. I'm still unclear on how to put all this in one module. I'm having trouble finding information.

And on my SQL DB table structure:

1. do I design them the right way,

2. am I right by assigning all the FKeys within the script (since all the other sql files from different modules seem not to have any FKeys, unless I'm missing something),

3. do I need to link my list tables to the main tables by IDs, or should I link them to insert the actual string and forget about conserving space issue, or do I need to link them at all (just pull the value from the list tables within the vb code and put them where I need)

Thanks,

Waldis ...

Actually I found the Blog and Repository modules to be the best at learning two fundemental ways of building a complex module.  My first module had 14 tables, 57 stored procedures, 32 ascx controls, and am still going (a reporting section- and it is made up of 3 module folders - like the repository/dashboard).  I'm still not done - and floundered for the longest time until I studied those. 

The events module was still a simple module - even though it had different controls, the blog, forum and repository helped me grasp the interplay of multiple controls per definition (few definitions using a controller) vs. fixed # controls per definition and several definitions - that all get placed on the page at once.  That was the key - adding several controls to one container on a page at once (like the Blog) - which I used for master data entry and control, or a set module control added with multiple views (I used this for the transactional data and reporting modules) - like the repository or forum modules.