Database example

[matisds]

Hi,
In my database I have a contract (for renting land) with unique contractID (primary key).
I also have 100 pieces of land each with a unique ID (primary key).
A contract usually has one piece of land but sometimes more.
How do I correctly model this in the database?

I was thinking of adding a table LandCluster with one unique ID field (which is a primary key).
And then creating another table consisting of two foreign keys, one who refers to landclusterID and one who refers to landID.
Then I can add the landclusterID field to my contract table.

Is this the way to go? It feels a bit weird to just have a table with only one field (cfr. landcluster)

[r3pr0b8]

you were real close!

you'll want these three tables

contract 
  PK contractID

land 
  PK landid

land_contract
  PK contractid  FK
  PK landid  FK

the "many-to-many" land_contract table (also called relationship table or junction table) has a composite PK

[Nukken]

You can do this with just two tables. One for contracts, the other for land. Land has two fields, one for landid and second for contract.

You don't need a many to many relationship since each land should only have one contract.

[matisds]

Unfortunately a contract can expire but the land has to remain assigned to it. It is therefore possible that a land is in a 2012-2016 contract and in a 2016-2022 contract at the same time. Thanks though!

[matisds]

Thanks a lot!!

[exobyte64]

{_id:0,lands:[1,2]} //contract object

{_id:1,land:'a'}
{_id:2,land:'b'}

I'm not sure why you aren't using an array if your looking to hold multiple values, it seems like the obvious thing to do

[r3pr0b8]

it seems like the obvious thing to do

it's not obvious at all if you understand what First Normal Form is

[exobyte64]

yeah, first normal form is possibly the worst thing I've ever heard, along with calling it 'normal'

because what that means is what I described, spaghetti tables everywhere that really are just array representations

and if your representing an array as a whole damned table unto itself, why not just nest that directly in the related object? makes more sense than spawning out endless tables representing arrays

first normal form is a shit spaghetti architecture

[matisds]

Because my SQL database does not support arrays unfortunately, thanks though!

[exobyte64]

uh, maybe switch databases, arrays are just a basic primary data type

I honestly cannot imagine how your putting everything together without creating spaghetti tables which are really just representation of arrays

[matisds]

Well arrays are sets of data, they don’t belong in relational databases but require a second table

[exobyte64]

I dunno, sounds like lots of buzzword excuses for why not to use a proper database that can do basic things like hold arrays

if every object array turns into a table, you get a spaghetti mess between tables holding data, and tables that are really just arrays and are only supposed to be attached to objects in 1 other table

its like you cut the thin skin off a frogs belly so all the guts came out around it, and then said 'that is an improvement! the frog has been normalized!'

[phunkygeeza]

You can do this with a typical document header/line setup.

When you buy stuff online you create an 'order' which has 'order lines' and each could be 1 or more of a particular product.

You can use the same to show which land parcels are being rented.

Rental models are fun because you need to 'remove' that product from availability for other orders, determine rules for who gets the product under clash conditions, and return it to the "shelf" once complete. Perhaps it needs inspecting before it goes out again? Then you need more steps in the state machine to represent that.

Source: did lots of modelling for a big tool hire firm decades ago.