PHP/MySQL CMS: data modelling & PHP workarounds

[NickZA]

Hi all

 

I am creating a bespoke CMS in PHP for a property developer. The CMS will support:

 

-Registered Users

-A simple forum which has separate areas for each property development

-A sitemap application I have built in Flash which lets one browse a development as an overhead map, viewing details of residences for sale.

...among other things.

 

These various functionalities share some information, a good example being the list of property developments the company presently holds. So this sort of data should be centralized so that all components agree with one another without using redundant data.

 

My problem comes in here: Basically, this company has many developments. Each development can contain plots, which may or may not be considered units unto themselves.

 

***Before reading further, view the diagram I have attached for an explanation. ***

 

Now, at least for the Flash part (which I did some time ago), I  implemented this structure as XML since XML is good for representing hierarchies. Now, however, I need everything to be centralized, and the forum, user management etc. are all in a MySQL database.

 

I'd rate myself as novice with SQL, although I did take it as a major in college years ago (so I may have potential). But essentially, I figure that things like table joins (or views??) might be required to implement this the right way. I haven't looked into it yet -- I'm scared. That's why I'm posting here.

 

How can I implement a hierarchy of this sort that is dynamic enough to allow an Admin to add new developments/properties through the rather simplistic backend CMS? Surely creating tables for new developments every time is not the answer? Or is it?

 

The other option I see is having a table listing all the developments; and a seperate table listing all units in all developments (but with a column saying which development each belongs to). But then, how do I implement the special case where an individual plot is actually a set of units as in the case of the apartment block? Ok, I could have a column which says what apartment block it's in (if it is in one)... but I fear that could quickly get very messy.

 

The reason I am not posting this on a MySQL forum is because PHP gives me the ability to manipulate the db in ways that might allow me to work around these potential issues, thus allowing my data modelling to be less than perfect but still allow me to get a working CMS going.

 

Looking forward to any and all answers, questions, etc. I will happily elaborate further if necessary.

 

Thanks all!

 

-Nick

 

PS. Please note that the way I've drawn the diagram should not imply whether or not the Developments will be represented as a list in one table, or each as a seperate table in the database... i.e. how it will be represented is exactly what I am asking advice on!!

 

 

 

 

 

[attachment deleted by admin]

[Barand]

perhaps something simple like this where a development can have one or more plots and each plot can have one or more units

[pre]

development                plot                    unit

-----------                ------------            ------------

devID      -------+        plotID      -----+      unitID

devName          |        plotSize          |      unitType      (house, aptmt)

devLocation      +-----<  devID            |      rooms

                                            |      beds

                                            |      baths

                                            +----< plotID

[NickZA]

Hi Barand

 

Hmm, yep, in fact that could work... I would have to setup my Flash db code to check how many units there are in a plot; if there is only 1, it would classify it as a house, otherwise, as an apartment block. For all other functionality, this setup would work in a very straightforward fashion.

 

I'm going to have to look into a bit more closely but I think you've helped me get on the right track -- many thanks.

 

Will post back here if I encounter any problems with this method.

 

Cheers!

 

-Nick

[NickZA]

Hi again

 

I've realised I need to step up my act a bit to get this database to work properly. Basically what I need to do is outlined here:

http://www.thescripts.com/forum/thr...te+foreign.html

 

In the 'units' table, I am trying to use the plotID (FK) and the unitNum fields as a composite PK. But since this PK would have to be referenced elsewhere as a FK, this obviously creates issues. So in the link above I saw the solution of using a surrogate field which represents the composite PK, then I can reference this surrogate elsewhere as a FK, solvign my problem.

 

But -- I am having trouble creating the surrogate key mentioned in that post; obviously it has to be autoincrementing, but I get the error "1075 - incorrect table definition; there can only be one auto column and it must be defined as a key".

 

This makes no sense really as the surrogate key column does not need to be a PK, that's the whole point -- it's acting as a surrogate for the combination of two other columns which actually are the PK (composite).

 

Am I misunderstanding something? What do I need to do here?

 

-NickZA

[Barand]

Your link doesn't work.

 

Do you want

 

CREATE TABLE unit
(
    plotID int not null,
    unitNo int not null autoincrement,
    unitOtherStuff varchar(10),
    PRIMARY KEY (plotID, unitNo)
);

 

This will autoinc the unitNo within each plot, giving something like

[pre]

Plot    Unit

------+-------

  1      1

 

  2      1

  2      2

  2      3

 

  3      1

 

  4      1

  4      2

  4      3

  4      4

  4      5

  4      6

[/pre]

[NickZA]

Hi again Barand

 

What you've outlined (while logical) is not quite the thing, since the CMS admin needs to be able to add unit numbers here and there as those units become ready for sale. Typically with housing developments that's not in any logical order (as an autoincrement would create). So the user needs to be able to submnit any arbirtray number and have it become the key (or part of, anyway). As you said, I need to use the unit number ("natural" key?) as a unique identifier when combined with the plotID.

 

Many thanks for your help.

 

-NickZA

[NickZA]

Oh also -- the previous link was:

 

http://www.thescripts.com/forum/thread647242-composite+foreign.html