# A couple of database design questions **URL:** \ **Category:** Rails\ **Created:** [December 4, 2007, 2:11am UTC](https://www.ruby-forum.com/t/a-couple-of-database-design-questions/122039 "2007-12-04T02:11:38Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![brian](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/97f17d/32.png) [@brian](https://www.ruby-forum.com/u/brian)\ **Post date:** [December 4, 2007, 2:11am UTC](https://www.ruby-forum.com/t/a-couple-of-database-design-questions/122039/1 "2007-12-04T02:11:38Z")
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 ---
**Author:** ![brian](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/97f17d/32.png) [@brian](https://www.ruby-forum.com/u/brian)\ **Post date:** [December 4, 2007, 4:03am UTC](https://www.ruby-forum.com/t/a-couple-of-database-design-questions/122039/2 "2007-12-04T04:03:35Z")
> 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. ---
**Author:** ![brian](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/97f17d/32.png) [@brian](https://www.ruby-forum.com/u/brian)\ **Post date:** [December 4, 2007, 4:32am UTC](https://www.ruby-forum.com/t/a-couple-of-database-design-questions/122039/3 "2007-12-04T04:32:11Z")
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. ---
**Author:** ![brian](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/97f17d/32.png) [@brian](https://www.ruby-forum.com/u/brian)\ **Post date:** [December 4, 2007, 8:27pm UTC](https://www.ruby-forum.com/t/a-couple-of-database-design-questions/122039/4 "2007-12-04T20:27:48Z")
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:](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 ---
**Author:** ![brian](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/97f17d/32.png) [@brian](https://www.ruby-forum.com/u/brian)\ **Post date:** [December 4, 2007, 8:18pm UTC](https://www.ruby-forum.com/t/a-couple-of-database-design-questions/122039/5 "2007-12-04T20:18:07Z")
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