jeudi 21 mai 2015

Universal App with SQLite database design

Quick question about database design when using SQLite in a universal app.

Previously, I used to define my models, add them to lists and serialize that list and save/load it and use linq to join them when required. Below is a simple example:

public void LoadCars
{
    List<Car> cars = new List<Car>();
    cars = LoadCars();
}

public class Car
{
    public int Id { get; set; }
    public string name { get; set; }
    public List<Parts> Parts { get; set; }
}

public class Parts
{
    public int Id { get; set; }
    public string name { get; set; }
}

I know that from the above, there wasn't any normalisation of any kind but this worked well to link my parts to a specific car.

Now if I want to move to SQLite, I'm wondering if I should introduce a foreign key table:

Table: Cars Id: int (Auto-Increment) Name: string (30)

Table: Parts Id: int (Auto-Increment) Name: string (30)

Table: PartsInCar Id: (int) Auto-Increment CarId (int) PartId: (int) Foreign (CarId) references Cars(id) Foreign (PartId) references Parts(id)

First, is this is the correct way to go? Should I design it this way or is there a better way?

Assuming it is, should I then use linq to get the parts for a specific car using inner join or should I use SQLite?

I know if it was a regular app, I'd go with this kind of design for my database, but I'm not sure whether or not this is the way to go for a UAP.

Thanks.

Aucun commentaire:

Enregistrer un commentaire