StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Given your business rule that each car belongs to at least one owner (ie. owners exist before they are assigned to a a car) and your operational constraint that the table may grow large, I'd design the schema as follows:</p> <p>(generic sql 92 syntax:)</p> <pre><code>CREATE TABLE Cars ( CarID integer not null default autoincrement, OwnerID integer not null, CarDescription varchar(100) not null, CreatedOn timestamp not null default current timestamp, Primary key (CarID), FOREIGN KEY (OwnerID ) REFERENCES Owners(OwnerID ) ) CREATE TABLE Owners ( OwnerID integer not null default autoincrement, OwnerName varchar(100) not null, Primary key(OwnerID ) ) CREATE TABLE HistoricalCarOwners ( CarID integer not null, OwnerID integer not null, OwnedFrom timestamp null, Owneduntil timestamp null, primary key (cardid, ownerid), FOREIGN KEY (OwnerID ) REFERENCES Owners(OwnerID ), FOREIGN KEY (CarID ) REFERENCES Cars(CarID ) ) </code></pre> <p>I personally would not touch the third table from my client application but would simply let the database do the work - and maintain data integrity - with <code>ON UPDATE</code> AND <code>ON DELETE</code> triggers on the Cars table to populate the <code>HistoricalCarOwners</code> table whenever a car changes owners (i.e whenever an UPDATE is committed on the OwnerId column) or a car is deleted.</p> <p>With the above schema, selecting the current car owner is trivial and selecting historical car owners is a simple as</p> <pre><code>select ownerid, ownername from owners o inner join historicalcarowners hco on hco.ownerid = o.ownerid where hco.carid = :arg_id and :arg_timestamp between ownedfrom and owneduntil order by ... </code></pre> <p>HTH, Vince</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload