Advice on a basic design, first time database design

I am taking a course to become a Java developer.

The course involves using databases but we never really design any, unfortunately.

Most of the time we get pre-made databases and we have to implement code on it to Insert, Update, Read or Delete data.

But when my final test comes, chances are I will be making something that involves a database and therefore I want to try designing a few smaller ones to get the hang of a designing since I noticed that a good database design makes a lot of difference when writing the code to work with it.

I hope these types of questions are allowed here and that it's not going to be too broad.

This is a small design I made for something a simple as clubs and members with addresses and phonenumbers.

• There will be multiple clubs.

• Each club will have multiple members (obvious)

• A member cannot be part of multiple clubs

• A member can have multiple phonenumbers and email addresses

• A member can only have one address

• An address can belong to different members (couples or siblings)

My biggest confusions:



-
If I want to add a column in Club that describes the owner, who will also be a member, what is the best approach without having the
same member listed twice?

-
Should I put all my tables on the automatic increment of an id or would this be a bad idea? (benefits/downsides?)

-
If I add foreign keys in the foreign keys tab, will these automatically correspond to the correct table or do I have to add
these to the columns also? (see picture 2)

-
And my probably noobiest question at all... Do I put the foreign keys of phonenumber and email in their respective table
linking to a person_Id or should I put phoneNumber and email Id's in
the person table?


(My apologies for the language in the pictures not being English, I hope this isn't too much of a problem)

Responses to your individual questions

If I want to add a column in Club that describes the owner, who will also be a member, what is the best approach without having the same member listed twice?

If —as stated in your specifications— a Person can be a Member of only one Club, and you are interested in storing this datum simply as true or false, one option is adding a BIT(1) or a BOOLEAN (TINYINT) column to the Person table, and you may wish to call this column IsClubOwner. In this way, you can register the fact that a determined person is a club owner exclusively one time. Apart from that, this method also allows the possibility of retaining several people as owners of the same club occurrence. You can see a logical level depiction of this approach in Figure 1.

However, you are looking for the best approach and, according to my experience, such approach entails the development of a much more expandable and versatile structure. In this respect, follow the progression of a modeling exercise for these and other points below, in the sections entitled “Covering your remaining specifications”, “Person as Member of multiple Clubs” and “Member and Owner as separate entity types”.

Should I put all my tables on automatic increment of an id or would this be a bad idea? (benifits/downsides?)

If you face an explicit requirement that indicates the definition of a table with a column of such characteristics, and that column has a valid contextual meaning or serves a particular purpose like being a surrogate for a wide natural PRIMARY KEY (PK), then yes, you should proceed that way.

Otherwise, if you do not have said requirement, I consider it would be unnecesary since you have to store and manage a meaningless extra column and (perhaps?) also an additional INDEX in your database.

As usual, you have to analyze each case along with its overall repercussions in order to decide how to carry on.

If I add foreign keys in the foreign keys tab, will these automatically correspond to the correct table or do I have to add these to the columns also?

In this regard, my advice for you is to create your database structure manually, to code your own DDL statements until you get a firm grasp of the subject. If you do so, it will be easier for you to understand the processes that graphical tools are performing “under the hood”.

To me, for instance, creating a statement like the following:

CONSTRAINT FK_PersonPhoneNumber_to_Person FOREIGN KEY (PersonId)
REFERENCES Person (PersonId)

Is much more instructive than using GUI tools to execute this kind of tasks, especially now that you are building your first designs.

And my probably most noobiest question of all... Do I put the foreign keys of phone_number and email in their respective table linking to a person_id or should I put phone_number and email ids in the person table?

Personally, I think that this and all your other questions are completely valid and well contextualized.

Returning to the technical aspects that concern us, this precise inquiry offers a good opportunity to review the two following assertions:

• A Person can be reached through zero-one-or-many PhoneNumbers

• A PhoneNumber can be used to reach one-to-many People

So, one can conclude that there is a many-to-many relationship between Person and PhoneNumber, therefore, I suggest the creation of an associative table named PersonPhoneNumber to represent said relationship in your database. The PK of this table should be composed by two different columns: PersonId (a FOREIGN KEY [FK] pointing to Person.PersonId) and PhoneNumber (a FK that makes reference to PhoneNumber.Number). For a logical level description of all of the above, see Figure 1.

On the other hand, let us examine the two propositions that follow:

• A Person can be contacted via zero-one-or-many EmailAddresses

• An EmailAddress can be used to contact exactly one Person

Then, yes, you should set a FK referencing to Person from the EmailAddress table, and this table should have a composite PK too, which would be comprised of the columns PersonId and Address. In this manner, you can ensure that the same combination of EmailAddress.PersonId and EmailAddress.Address can be inserted only once.

If you also wish to ensure that a given EmailAddres.Address can be stored in one sole row, you just have to establish a UNIQUE CONSTRAINT for this column.
Proposed logical data models

In order to expose my suggestions more clearly, I included four distinct IDEF1X[1] logical models that are shown in Figure 1, Figure 2, Figure 3 and Figure 4. I will provide an explanation of the most relevant features displayed in each one of them in the corresponding sections. You can as well download from Dropbox a PDF that integrates in a single model the majority of the elements under discussion.
Covering your remaining specifications
Relating People and Addresses

Let us inspect the two following (s

StackExchange Database Administrators Q#104409, answer score: 13