1

I have a database with 3 tables.

Table Work, with a field named workName.

Table SubWorks, which have a field subWorkName and work with a relationship with Work.workName.

And table Activity with field superworkname which should have a relationship with SubWorks.subWorkName.

I can't create this relationship because SubWorks.subWorkName is not an unique key (or primary key, it just don't appear in phpMyAdmin) and it can't be because it's allowed to have multiple SubWorks with the same name until Work.workName is different between them.

How can i resolve this situation? I'm thinking about using a primary key int, but what if one day i go in overflow? Suppose i got so much works to store that i go out the limit?

I added an ID column to Activity so i can differentiate them

That's what i want to do with relationships:

I have a Work named R1.

3 SubWorks, named: R1-1, R1-2, R1-3 with a relationship to R1.

And 2 Activity (ID: 1, 2), with refer with subWorkName to R1-1. And another activity with ID 3, which refer with subWorkName to R1-2

I want, if i delete R1: R1-1, R1-2, R1-3 will be deleted too and with them all their activity, so activity 1 and 2 will be deleted to.

Now, suppose i delete R1-1, activity ID 1 and 2 should be deleted too.

If i delete R1-2 activity ID 3 should be deleted.

I already did the first part (if i delete a Work, SubWork will gone with it) but i had problem with the second.

Improve this question

2 Answers 2

Reset to default
0

Using surrogate IDs is one way but you don't really have to. If you want to keep your current design - which I suppose has Work (workName) as primary key and SubWork (workName, subWorkName) as a potential primary or unique key, you can define the Activity table like this (I also changed slightly the column names):

Work -------- workName PK SubWork -------- workName PK , FK -> Work (workName) subWorkName PK Activity -------- activityID PK -- or what other column you have as primary key workName FK1 subWorkName FK1 FK1 (workName, subWorkName) -> SubWork (workName, subWorkName)

As @raumkrieger points out, to define a foreign key constraint you have to reference a unique or primary key column (or columns.) Since if we assume correctly that (workName, subWorkName) unique identifies a row in the SubWork table, you can use this combination to be referenced by the foreign key.

Note: I do not suggest that using long varchar columns is always better than using narrow surrogate integer columns. Especially if the tables are big (or plan to be) and/or the varchar columns are very wide, this will not be the most efficient design in most DBMS. But for narrow varchar columns and/or smaller tables, the difference in performance will be negligible.

Improve this answer
5
  • Making subWorkName of SubWork Primary Key then i would not be able to create a new SubWork with the same name but different workName. Right? Commented Jun 25, 2014 at 16:45
  • @MarcoAcierno No, my suggestion is to make the combination (workName, subWorkName) the primary key of SubWork. So, you could have 2 subworks with same name and different workname. Or with same workname and different name. But not 2 rows with same workname and same name. Commented Jun 25, 2014 at 18:04
  • What is activityColumn? Commented Jun 26, 2014 at 10:28
  • I edited my answer with what i want to do. Commented Jun 26, 2014 at 11:12
  • I should have named that ActivityID or something similar. You had not mentioned that table's primary key. Commented Jun 26, 2014 at 11:47
2

Primary keys (and foreign keys) should be unique, otherwise how does the database know which row the foreign key relationship is pointing to?

I'm thinking about using a primary key int, but what if one day i go in overflow? Suppose i got so much works to store that i go out the limit?

I wouldn't worry about hitting the limit for ints. The mysql documentation lists the limits of various int types. If you're concerned about having more than 2 billion rows anytime soon, you can always use bigint and have over 9 quintillion rows before overflowing. 18 quintillion if you used unsigned.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.