Little help with normalisation...

[Trogan]

Hi,

I need some help with normalisation please. I need to enter an event multiple times using the same event number under the "supply" table, however I receive the "unique constraint violated" error.

I have attached my normalisation up to 3NF, but believe I have messed up somewhere.

I'd appreciate any help/explanation on what I need to do as I am really stuck.

Thanks.

[timothybard]

You need a Supply ID primary key in the Supply table with event no and wedding no as foreign keys. The way you have it set up currently, each event/wedding can have only one supply (I'm assuming the underlines indicate primary keys).

[Trogan]

Hi Timothy,

Thanks for your reply. I did what you said and I can now enter multiple events with the same event number - thanks.

I do have one more question: the tables I am normalizing do not include any SupplyID or SupplyNo. In cases like these, would I automatically create a SupplyID or SupplyNo to make the table unique? What about having a composite key?

Could you explain this to me briefly please? Thanks again.

I'm assuming the underlines indicate primary keys

Yes and foreign keys.

[timothybard]

The particulars will always be determined by your database needs, however, it is rather common for each different table to have its own ID, which is the primary key for the table. If a table has a primary key, then no two records can have the same value as the primary key. The primary key is simply a way to ensure that all records are unique.

It is common to have the database management system, such as MS Access, MS SQL Server, mySQL, etc. generate the primary key. It is usually just a sequential sequence of integers (1, 2, 3, 4, etc). You just need to make sure to tell the database management system which field is the primary key and it takes care of generating the values.

It is possible to use composite keys, but it would not work in your situation with the supply table because there is a one-to-many relationship between supply table and the event table. Secondly, there is a direct relationship between the event table and the wedding table, so using the wedding id as part of the composite key does not help to make the key unique. A composite key would be more appropriate in a situation where there are two one-to-many relationships from one table to two or more separate tables and there is only one record that is associated for any two particular records. The composite keys you have in the Event_Guest and Event_Venue tables are good uses of composite keys.

[Trogan]

Thank you for the detailed explanation.

One last question, if you don't mind: I have created the composite keys for the Event_Guest and Event_Venue. However, is it necessary to create an Event_Supply table? If I was to create one, would a composite key work bearing in mind the Supply table has 1 PK and 2FK's?

Thanks again. :)

[timothybard]

It appears that there is a many-to-many relationship between the supplies and events (each event can have more than one supply and each supply can be used for more than one event). If that is the case, then an Event_Supply table would be appropriate. The Event_Supply table would have a composite key of event_no and supply_no. The supply table would not have a composite key and would have a primary key of only supply_no and would not need any foreign keys. Having the foreign keys would mean that each supply could only be used for one event.