Go4Expert

Go4Expert (http://www.go4expert.com/)
-   MySQL (http://www.go4expert.com/forums/mysql-forum/)
-   -   db normalisation (http://www.go4expert.com/forums/db-normalisation-t17127/)

fotoart 26Apr2009 01:43

db normalisation
 
hello evevryone

im trying to normalise my db.

Im stuck at an instance where i find that a certain column (info)
is appearing at every table.
What should I do?

my db is about a movie album
the problem is every movieclip has a caption and also an info page which
is appearing in every table.
And each of these captions and info comes in different languages. lets say english, french german and so on.

any idea how I can go about setting up the tables?

any help would be appreciated.

I happen to know the basics of normalisation
and I can resolve the matter without the langugaes table.

thanx
ahmed

shabbir 26Apr2009 09:12

Re: db normalisation
 
Have a Movie ID which stores the caption and info page into separate table and not in every table

fotoart 28Apr2009 02:21

Re: db normalisation
 
Hello Shabir
thanx for the reply.

Idont know how I can set up a separate
table for fotos.
my db is about hotels and their amenities/services.
for simplicity I have created few tables as follows


-------------------
Hotels
-------------------
Hotel A
Hotel B
Hotel C


-------------------
GuestRoomTypes
-------------------
Grand Suite
Presidential Suite
Classic Suite
Deluxe Suite



-------------------------------
Hotel-GuestRooms
-------------------------------
Hotel A, Grand Suite
Hotel A, Presidential Suite
Hotel B, Grand Suite
Hotel B, Presidential Suite


---------------------
Activities
---------------------
catamaran Sailing
water Skiing
table Tennis
Tennis
Golf



Hotel-Activities
-----------------
Holiday Inn, Catamaran sailing
Shangri La, Golf
Shangri La Tennis



--------------
photos
--------------
????


Can u help me setup the fileds for the photos table.
I have tried few ways and didnt workout.

thanx
ahmed

fotoart 28Apr2009 02:25

Re: db normalisation
 
Sorry heres a correct version of the post

Hello Shabir
thanx for the reply.

Idont know how I can set up a separate
table for fotos.
my db is about hotels and their amenities/services.
for simplicity I have created few tables as follows


-------------------
Hotels
-------------------
Hotel A
Hotel B
Hotel C


-------------------
GuestRoomTypes
-------------------
Grand Suite
Presidential Suite
Classic Suite
Deluxe Suite



-------------------------------
Hotel-GuestRooms
-------------------------------
Hotel A, Grand Suite
Hotel A, Presidential Suite
Hotel B, Grand Suite
Hotel B, Presidential Suite


---------------------
Activities
---------------------
catamaran Sailing
water Skiing
table Tennis
Tennis
Golf



Hotel-Activities
-----------------
Hotel A, Catamaran sailing
Hotel B, Golf
Hotel B, Tennis



--------------
photos
--------------
????


Can u help me setup the fileds for the photos table.
I have tried few ways and didnt workout.

thanx
ahmed

shabbir 28Apr2009 08:42

Re: db normalisation
 
Ahmed

I assume

Hotel A
Hotel B
Hotel C

and

Grand Suite
Presidential Suite
Classic Suite
Deluxe Suite

Are ids and not the data.

If that is the case Have a Table where your unique identifier is the identifier of the photo.

Photos
--------
Photo Id Photo Data

And then Photo Id should be mapped to Hotel / GuestRoomTypes and other things.

Also Just a note never have the table name as plurals

fotoart 28Apr2009 14:42

Re: db normalisation
 
shabir to make things clear, they were actually data

the tables with unique IDs are as follows



--------------
Hotel
--------------
hotelID, hotel




--------------------
roomType
--------------------
roomTypeID, roomType




--------------------
activity
--------------------
activityID, activity




they are all m:m tables
So I made join tables like so

------------------
hotel-roomType
------------------
hoteldID, roomTypeID


------------------
hotel-activity
------------------
hotelID, activityID




my problem is setting up photo table

for instance there can be a unique ID but what comes next?

photo
------------
photoID,???

thanx for the help
ahmed

shabbir 28Apr2009 16:38

Re: db normalisation
 
Photo Id and Photo Content like URL / FilePath / Binary Data

fotoart 28Apr2009 18:10

Re: db normalisation
 
hello Shabbir
Once again thank you for helping me out.

What u recommended sounds right. But the part I dont understand is just giving a code and entering a photo.
For instance, I cannot say photo 001 and enter it in the photo table. it will be lost forever.
I know the URL will be there but u cannot associate it with any table.
There will be no way of mapping it to another table.

In other words photo table will not be proper with a uniqueID and a url.
I hope u under stand what im sayin.

thanx
ahmed

shabbir 28Apr2009 20:06

Re: db normalisation
 
001 photoid should be mapped to Some HotelID in other table.


All times are GMT +5.5. The time now is 04:37.