SQLite one to one displayed as optional one to many

[drsjb80]

1. Please check DbSchema Help / Output logs for errors. No errors, but the line 07:06:37 REVERSE ENGINEERED 4 TABLES FROM Sqlite. might be indicative.

2. Please include the DbSchema version, operating system and used database 9.3.0 and SQLite3.

3. The steps to reproduce this issue I'm using Rails and SQLite3. Here's the statement in the schema

CREATE TABLE IF NOT EXISTS "people" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar, "phone_id" integer NOT NULL, "created_at" datetime(6) NOT NULL, "updated_at" datetime(6) NOT NULL, CONSTRAINT "fk_rails_15f655d702" FOREIGN KEY ("phone_id") REFERENCES "phones" ("id") );

I was expecting the relation between people and phones to have the two lines (one and mandatory) but I'm seeing multiple and optional (see attached image). I'm sure I'm missing something, can someone point me in the right direction? Thanks!

[wise-coders]

Phone_id can be null as there could be no record in people pointing to a given phone. This can be enforced only using triggers. And there could be multiple peoples using the same phoneid, as the phone_id is not indexed unique.

We had some improvements in the notation after we introduced the logical design. We had to use the same conversions for both, logical and physical.

[drsjb80]

Okay, I get that phone_id isn't unique here, but when I do

sqlite> INSERT INTO people VALUES(3, 'Bob Smith', NULL, '2022-09-18 15:45:42.252176','2022-09-18 15:45:42.252176'); Error: NOT NULL constraint failed: people.phone_id

which seems to indicate non-optional. Rails does say:

The Active Record way claims that intelligence belongs in your models, not in the database. As such, features such as triggers or constraints, which push some of that intelligence back into the database, are not recommended. (https://edgeguides.rubyonrails.org/active_record_migrations.html#foreign-keys)

so certain aspects of the DB are being enforced by Rails, but NOT NULL in people for phone_id does seem to be enforced by SQLite3, making it non-optional, but I might be using the wrong term here.

Thanks for the response above! I'll continue to figure this all out. Love your program!

[Psyclo1]

The relationship between Phones and People can be read two ways. First, from Phones to People, it is Zero-to-Many, since I can define Phones that aren't associated with People. Technically, the People table could be empty, and I could have a thousand Phones records, perfectly fine. Furthermore, every People record could conceivably have the same phone_id value, so that is the "many" aspect of the relationship, many People could have the same Phone. From People to Phones is a mandatory 1:1. If I create a record in People, since the column is mandatory, it must have a value, and that value must be a valid Phones ID. You can change the FK notation to Information Engineering (without arrows), as shown in the attached picture, and it will show the line on the Phones side of the relationship indicating the mandatory 1:1. Also note that the line is solid, which in DbSchema indicates the mandatory FK column. If you changed phone_id to nullable, the line would become dashed, and the Phones end would become a circle.

Now, to what I think you may have intended, which is that a single person could have multiple phone numbers, which is much more typical. In that case, you would want to flip the FK, and have it be on the Phones table with a people_id column pointing to People, and remove the phone_id column from People. That way a People record could have zero to many Phones records, and a Phones record would refer to a single person in People.

Hope this helps. Mike Nicewarner

[drb80]

It helps a lot! I'll continue to work through all of this. And I agree with flipping the relationship, I'm not sure why I started with it the other way around. Thanks much!