List advice

[paulthepaddy]

Hi guys,

I'm looking a bit of advice of the best way I should construct the list/lists/Dbtable/DBTables :S

I'l try my best to stay on topic and keep it simple. I need to fill 3 properties Car.Make Car.Model Car.Colour, each property cancels out options for the next, eg VW would leave only Vw models available for selection and models would leave colours for that model.

Previously i had made each property a class and made 1 large serialized list, which if im honest wasn't the easiest to use more so for editing the list :S so i thing my options are as follows

1: serialize1 list
2: 1 list that contains all 3 coloums
3: 3 lists
4: DB table containing all 3 coloums
5: DB table for each property

I am using EF for my DB, and I need to display the data in ComboBoxes,
Any advice would be great, thanks guys.

 

[jmcilhinney]

There should be a Make table with a MakeId column as primary key. There should also be a Model table with a ModelId column as primary key and a MakeId table as a foreign key. There should also be a Colour table with a ColourId column as primary key. You would then have a ModelColour table that had a ModelId column and a ColourId column, both as foreign keys. The primary key might be the combination of those two columns or you might have a separate ModelColourId column. Your Car table would then have a CarId column as the primary key and ModelId and ColourId columns as foreign keys.

Note that there is no MakeId column in the Car table. The make of a car is fixed via the model. If you wanted a MakeId and/or a Make property in your Car entity then it would be a read-only property that you added yourself and they should simply pass through the Model.MakeId and/or Model.Make property values.

As for your UI, you would bind the full list of Make entities to the first ComboBox. In the SelectedIndexChanged event handler, you could use the Models property of the selected Make to populated the second ComboBox. In the SelectedIndexChanged event handler for that, you could use the Colours property of the selected Model to populate the third ComboBox. You would set the Model/ModelId and Colour/ColourId properties of the Car in the SelectedIndexChanged event handlers of the second and third ComboBoxes.

 

[paulthepaddy]

Hi jmc, Thanks for your reply I had started doing this, thought the DB tables would be the better long term solution, also the fact I would need to transfer files around the clients if doing the list. I did have to make a few changes to what I had done after followng your instructions. I am struggling to display the colours properly, I can display the colour id's from binging the ModelsColours from Models but I can't display the colour names. I have tried putting the ModelsColours into a variable and getting the colours that way but it failed to cast :S. I understand how Many To Many tables work, but have never been able to use them properly in my applicaton hence the reason I decided to use the Serializble List the first time round.

Am I missing something in the database or is it just my code?

Private Sub frm_CreateCar_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        With cb_Make
            .DataSource = db.Makes
            .DisplayMember = "Make_Name"
            .ValueMember = "Make_ID"
        End With

    End Sub

    Private Sub cb_Make_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cb_Make.SelectedIndexChanged
        With cb_Model
            .DataSource = CType(cb_Make.SelectedItem, Make).Models
            .DisplayMember = "Model_Name"
            .ValueMember = "Model_ID"
        End With
    End Sub

    Private Sub cb_Model_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cb_Model.SelectedIndexChanged
        With cb_Colour
            .DataSource = CType(cb_Model.SelectedItem, Model).ModelsColours
            .DisplayMember = "Colour_Name"
            .ValueMember = "Colour_ID"
        End With

Thanks for taking the time to help and reply

 

[paulthepaddy]

Hi JMC, I got it working, I spent a bit of time googling and how to use Many To Many relastionships with EF, I noticed that EF manages the navigation properties for Many To Many so figured the culprit had to be my DB. I was using a seperate PK for the ModelsColours table but also noticed I had 2 of the same record, which gave loads of problems trying to sort out the PK's and FK's lol, had to copy the data out of the table leaving the 1 duplicate drop the table and recreate, but 1 duplicate of 436 records manually done wasn't too bad... lol.

All is working as it should so thanks for the help.

 

[jmcilhinney]

This is wrong:

        With cb_Colour
            .DataSource = CType(cb_Model.SelectedItem, Model).ModelsColours
            .DisplayMember = "Colour_Name"
            .ValueMember = "Colour_ID"
        End With

You're binding a list of ModelsColour objects and the ModelsColour class doesn't have a Colour_Name property so you can't specify that as the DisplayMember. Colour_Name is a property of the Colour class and you access a Colour object via the Colour property of a ModelsColour object. As such, the DisplayMember should be "Colour.Colour_Name".

By the way, that join table should be ModelColour, not ModelsColour.

Also, I really hate inconsistency in naming. There's really no reason at all to be using underscores in any of those names. Underscores are only useful if a name is all upper- or lower-case. If you're using came-casing then they are pointless. If you are going to use them though, use them consistently. If you're going to use Colour_Name and the like then you should be using Model_Colour too. If you don't use an underscore in ModelColour then why would you use one anywhere else either? Whatever you do do, BE CONSISTENT.

 

[paulthepaddy]

I was struggling as their wasn't a Colours property in ModelsColours, but now their isn't a ModelsColours table show it just shows the Many To Many relastionships and Models now has a Colours property.

With cb_Colour
            .DataSource = CType(cb_Model.SelectedItem, Model).Colours
            .DisplayMember = "Colour_Name"
            .ValueMember = "Colour_ID"
        End With

I am quite a fan of Proper Case and _ I find it the neatest and easiest to read, I didn't use it in ModelsColours table as it would be my only table with an underscore in it, I also don't see the ModelsColours table while in VS. If you think I should change it, then I will, but as far as I can tell ModelsColours would be my only inconsistant, you have said to me before about being consistant so I do try