A couple of database design questions

I’m designing a db with several types of organization-type entities,
e.g., company, school, etc… each one of which can have one or more
addresses, phone numbers, fax numbers, etc. Therefore, wouldn’t it be
best to give addresses, phone numbers etc. their own tables to have this
kind of flexibility?

Also, is it me or does the use of single table inheritance in your
database make it extremely difficult (if not impossible?) to use foreign
key constraints (FKC) when forming associations with other entities? If
so, this is not good, because I thought using FKC to mark associations
was considered best practice in SQL.

Any thoughts/comments on these most pressing matters would be
appreciated.

Thanks

I’m designing a db with several types of organization-type entities,
e.g., company, school, etc… each one of which can have one or more
addresses, phone numbers, fax numbers, etc. Therefore, wouldn’t it be
best to give addresses, phone numbers etc. their own tables to have this
kind of flexibility?

Yes. I’d also think about adding a boolean field to indicate the
“primary” address. Then you can create a has_one association to fetch
the
primary address easily.

Brian B. wrote:

I’m designing a db with several types of organization-type entities,
e.g., company, school, etc… each one of which can have one or more
addresses, phone numbers, fax numbers, etc. Therefore, wouldn’t it be
best to give addresses, phone numbers etc. their own tables to have this
kind of flexibility?

Sometimes, sometimes not. You may find it more useful to de-normalize
to a certain extent and have a single “contacts” or perhaps “locations”
table which combines addresses and phone numbers, depending upon how the
information is commonly grouped and retrieved. Your design process will
have to ferret this out.

Also, is it me or does the use of single table inheritance in your
database make it extremely difficult (if not impossible?) to use foreign
key constraints (FKC) when forming associations with other entities? If
so, this is not good, because I thought using FKC to mark associations
was considered best practice in SQL.

DHH evidently does not like DBMS level consistency constraints of any
sort and so FK is not part of Rails (so far). I, and many others, do
not subscribe to this school of thought. FK constraints are, in my
opinion, an absolute requirement for relational data implementations
because the data exists independently of the applications that access
it. In any large scale system, at some point Ruby and Rails are only
going to be one of the ways the data is accessed, used, and modified.
Once that happens, all your carefully crafted AR models are so much
cruft and only the DBMS stands between your data and whatever is trying
to screw you up. Foreign keys, stored procedures and triggers should
all be employed to the maximum extent possible to protect your data from
all comers.

On Dec 4, 2007, at 1:18 PM, Ilan B. wrote:

I thought that it wasn’t a question of whether he liked them or not
but
that it was non trivial to implement in a vendor neutral fashion as
FKCs
vary greatly in their syntax from one vendor to the next.

And some don’t even support them: SQLite3.

From http://www.sqlite.org/omitted.html:

FOREIGN KEY constraints are parsed but are not enforced. However, the
equivalent constraint enforcement can be achieved using triggers.

Phillip

James B. wrote:

Brian B. wrote:

DHH evidently does not like DBMS level consistency constraints of any
sort and so FK is not part of Rails (so far). I, and many others, do
not subscribe to this school of thought. FK constraints are, in my

I thought that it wasn’t a question of whether he liked them or not but
that it was non trivial to implement in a vendor neutral fashion as FKCs
vary greatly in their syntax from one vendor to the next.

Please correct me if I am wrong as I often pondered on this as well…

ilan

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs