CODEWITHSUNDEEP
sqlsql-server
Mike Diaz
Mike Diaz

Reputation: 2065

Restructuring of Database/Tables

Let's say I have a database that has these tables

Car --> Has Dealerships --> Has Demographic --> Has Revenue

Later after launch the client says they want the car to have model types and each model should have a dealership

So my structure becomes

Car --> Has Model --> Has Dealerships --> has Demographic --> Has Revenue

How would I migrate to the second database structure? I would need to sever foreign keys between the Car and Dealership for this to work. Would I just take a snapshot of the old database and write a wrapper in the context that knows how to deal with it?

Upvotes: 3

Views: 824

Answers (1)

Andriy M
Andriy M

Reputation: 77737

My understanding is it should be Model --> Has Cars, not Car --> Has Model. That would seem to me a more consistent use of the --> designation.

So, the correct final relationship chain would probably look something like this:

Model --> Has Cars
      --> Has Dealerships --> Has Demographic --> Has Revenue

I would imagine the following sequence of actions:

  1. Create and populate the Models table. Your schema at this point:

    Model

Car --> Has Dealerships --> Has Demographic --> Has Revenue

  2. Add a ModelID column to Cars and populate it with the correct Models references based on your business requirements. It'll give you the following result:

    Model --> Has Cars --> Has Dealerships --> Has Demographic --> Has Revenue

  3. Add a ModelID reference to Dealerships, populate it by joining Dealerships to Cars using the current relationship. You will end up having double relationship between Dealerships and Models, direct and through Cars, like this:

    Model      -->     Has Dealerships --> Has Demographic --> Has Revenue
    \               /
     \-> Has Cars -/

  4. Drop the Cars --> Dealership foreign key, drop the Dealerships.CarID reference column. This will be your final result:

    Model --> Has Dealerships --> Has Demographic --> Has Revenue
    \
     \--> Has Cars

Upvotes: 1

Related Questions