Posted by J.O. Aho on 06/14/06 18:00

Frankly wrote:
> "Frankly" <PHPmySQL1@yahoo.com> wrote in message news:...
>>
>>>> "J.O. Aho" <user@example.net> wrote in message
>>> Seattle WA98108,WA98109,...
>>>
>>> will be slower to search than
>>>
>>> Seattle WA98108
>>> Seattle WA98109
>>>
>>> and the fist one makes you can't have real relations between the ZipTable
>>> and other tables (JOIN won't work), so the later is always a better one.
>>>
>>>
>>>
>>> //Aho
>
> I started this a couple of days ago and have been away since.
>
> That is how I will do it.
> I remember a while back when we talked about the RelationManagementBuildings
> table.
> I wasnt sure if i should put a primary key there and you replied something
> like.
> it is a good idea to have "A" primary key in that table. Some of the stuff
> I
> read says that in some cases it might be better to have both ManagementID
> and BuildingsID set as primaries. However you said "A" primary key. I
> want to build this database according to what you think is best. do you
> think it is better to only use A primary key or should i set both. I am
> thinking that the relation table will hold the foreign keys.

Much depends on how the table looks and is supposed to work, in a relation
table you use the two foreign keys (ManagementID and BuildingsID) together to
be a primary key, this gives you the advantage that you won't get any
duplicates in your relation table.

While in the Management table you only have the ManagementID as the primary
key (you don't the foreign key BuildingsID at all as the relation is set in
the relation table).


> in the begining i felt it was better to explore my choices and build tables
> from within the Admin. however at this point you are so right. I just
> created the tables and here goes.
> I am not sure if Management and buildings table should hold the primary keys
> or
> the relations building should hold both.

It should have both the foreign keys together as primary keys (don't remember
the syntax offhand here how to set it).

You may ask why not set both of them as primary keys in the relation table,
then answer is quite obvious if putting your mind into it for a while, primary
key will automatically mean unique, which makes you can only have the ID once
in the whole table

table managementbuildingrelation
managementid(primary key) buildingid(primary key)

M001 B002
M002 B001

The following won't be allowed in the table

M001 B003

as M001 has already been there

table managementbuildingrelation
managementid buildingid [managementid+buildingid=primary key]

M001 B002
M002 B001
M001 B003

This time you can have all three lines in the table as M001+B002 isn't the
same as M001+B003.


> time to hold my nose and jump in - now the fun starts. i am a bit nervous
> about what I have already forgotten to do
> in the tables below. if you feel something looks like i didnt put enough
> effort into it please tell me the terms I should look up.
>
> Management Table
>
> ManID - Int Auto unless relations table holds both primary keys.

You still need the ID here, otherwise you can't use the relation table to
match the management with the right building. Int and auto is good. Keeping it
primary key too.

> Name - varchar (45)
> Contact - varchar (45)
> Phone - Varchar (45)
> Address - Varchar (45)
> City - varchar (45) default New York
> State varchar (2) default NY
> ZipCode - Varchar (10)
> Website - Varchar (50)
>
> Buildings Table
>
> BuildingID INT Auto

Same applies as for ManID.

> Name - Varchar (45)
> Address - varchar ( 50 )
> Cross streetA - Varchar 50
> Cross StreetB - Varchar 50
> WebSite - Varchar 50
> Picture - Varchar 50
> Elevator- varchar 1
> Laundry - varchar 1
> Pets - varchar 1 - sometimes they do allow pets that are smaller than 12lbs.
> so i am thinking pets comments feild.
> pets comments if pets = 1
> Gym -varchar 1
> video intercome varchar 1
> contact varchar 45
> contact phone varchar 45
> Area varchar 45
> Zip Code Varchar 45
>
> RelationManagementBuilding table
>
> ManagementID INT
> BuildingID INT

Yes, the look of them is okey, you only need to make the
ManagementID+BuildingID to be primary key.


> Apartments Table
>
> ApartmentID - int
> BuildingID - int

This works nicely a primary key and a foreign key, you could have used a
relation table, but in this case I think this (how you done9 would be better.

> Rent - dec
> AptNumber - varchar 6
> Bedroom - dec 3
> bathroom - dec 3
> WebSite varchar 50
> Picture varchar 50
> Notes - text - there will be 4 picture fields and 1 note field for each
> picture.
> MoveInDate - Date 0000-00-00
> Still Available Varchar 2
>
> ZipArea table
> ZipCode varchar 10
> Area varchar 45
>
> i feel like i am handing in a very important test and this is the easy part.
> how bad did i do.
> i know i have forgotten alot of what i read.
> after this I am going to look into creating relations using the admin tool.

I think this looks quite good, some sizes could be larger, specially for URLs.
Not sure what you want to store in bathroom, how many sqf or the number of
them, but if it's the number of bathrooms, then I would go with INT as you
never have a half bathroom.



//Aho

[Back to original message]