1

Is it bad to have two tables with the same attributes but with different meanings and relationships?

I.e. table1's attributes = table2's attributes.

However table1 has a relationship with table3 and table2 has a relationship with table4.

Is this considered bad practice? Or should table1 and table2 be collated into one table?

Although if it were to be collated into one table wouldn't it follow that table1 would also have a relationship with table4?

philipxy
  • 14,867
  • 6
  • 39
  • 83
zzzzz
  • 75
  • 2
  • 10
  • when the schema of the two tables is the same, it can imply some problems (i.e. join results cannot be interpreted without further knowledge, when you do have a tuple you have to know from which join is resulted, and a few others), however, when they have a different meaning, they HAVE to be separate tables because otherwise users could write joins they are not supposed to use, which might influence correctness of code. – DevilSuichiro Oct 11 '15 at 15:49
  • are you referring to spurious tuples? note that altough table1 and table2 have the same attributes, they are not related. Also, another question that may be unrelated to this, but is it considered bad practice to have a disjointed entity based only on distinct attributes? – zzzzz Oct 11 '15 at 16:13
  • 2
    If "has a relationship with" means "has a foreign key" then that implies there are differences in the attributes of these tables. Put the common attributes in one table (supertype). Put the attributes that are specific to one or other case into separate tables (subtypes) that reference the supertype table. Google for the Principle of Orthogonal Design - a design principle which says tables should not duplicate each other's attributes. – nvogel Oct 11 '15 at 17:20
  • ahh ok. So it would be considered bad practice to disjoint 2 entities solely based on the relationship differences?: refer to this [link](http://imgur.com/aVd0H9q) – zzzzz Oct 11 '15 at 17:26
  • 2
    It's not usually good practice in a database design. If you are talking about entities and ER modelling then that's a different matter altogether. You might want to do such things in an ER model depending on the purpose of your ER model. The Principle of Orthogonal Design is a design rule for relational modelling, not ER modelling. – nvogel Oct 11 '15 at 18:23
  • 1
    attributes are the same but tables have different meaning – zzzzz Oct 13 '15 at 11:29
  • @DevilSuichiro I agree that "when they have a different meaning, they HAVE to be separate tables". I can't make any sense of the rest. (But only base table meanings in terms of attributes matter to querying.) – philipxy Oct 13 '15 at 15:01
  • @zzzzz Do you mean "disjoint"? But what do "disjoint[ed] entity" and "based only on distinct attributes" mean? Please be clear. Use more words. Ask another question. (I suppose you are asking about subtyping of entity types.) (As addressed by sqlvogel.) PS "entity" is ambiguous. In ERM it means entity instance (row), but is also used for entity set (table value) & entity type (table type). – philipxy Oct 13 '15 at 15:12
  • ya, in hind sight i should have based the question off eer diagram inheritance. But what i meant was; in most inheritance instances i generally see the sub entities (by entities i mean relational schemas or tables) will have a attribute that's not apart of the parent entity, see here: [link](http://jcsites.juniata.edu/faculty/rhodes/dbms/images/specialization.gif) i was wondering if you could substitute the subclass attribute with a distinct relation that doesn't apply to the parent entity. i.e here[link] (http://imgur.com/aVd0H9q); ps sorry if my english is bad – zzzzz Oct 13 '15 at 15:29

1 Answers1

2

It is ok to have tables with the same attributes. Just find sufficient meanings/predicates to record your application state.

A base table has a "meaning" or "predicate (expression)" that is a fill-in-the-(named-)blanks statement given by the DBA. The names of the blanks of the statement are the columns of the table. Rows that fill in the blanks to make a true proposition about the world go in the table. Rows that fill in the blanks to make a false proposition about the world are left out. Ie a table holds the rows that satisfy its statement. You cannot set a base table to a certain value without knowing its statement, observing the world and putting the appropriate rows into the table. You cannot know about the world from base tables except by knowing its statement and taking present-row propositions to be true and absent-row propositions to be false. Ie you need its statement to use the database.

And just declare the relationships/FKs as appropriate. The R in ER is for relationship meaning association (among entities). However many self-styled "ER" methods and products use "relationship" for foreign key (FK) instead. A FK from some roles/columns in one association set/table to some in another just means that every entity/subrow for those roles/columns in the source must appear in the corresponding roles/columns of the target as a key. When that is so, declare the relationship/FK.

A constraint expression just corresponds to a proposition aka always-true statement about the world and simultaneusly to one about base tables.

(From this answer. See this answer re attributes/headings, this one re predicates and this one re relationships/FKs/links.)

Community
  • 1
  • 1
philipxy
  • 14,867
  • 6
  • 39
  • 83