phpmyAdmin - database help please

tincat

Hi all,

Firstly I'm a novice at PHP. I'm doing an assignment and am stuck on something.

I'm setting up a database from a class diagram that I've designed, and for some reason I can't set a foreign key.

There are about 10 tables, but the three that I'm concerned with are basically

person (personIdNo(PK) personFirstName personLastName personAddress etc)

staff (staffIdNo(PK) personIdNo(FK) branchIdNo jobDescription jobSalary etc)

customer (customerIdNo(PK) personIdNo(FK) etc)

1, is this a good way to set up the information to eliminate duplicating data? i.e. much of the info for staff and patients is the same data.

2, if this is good, can you tell me why when I try to set a foreign key in 'staff' or 'customer' I get the following error?

#1005 - Can't create table 'system.#sql-ce8_39a' (errno: 150)

The query I am running is as follows:

ALTER TABLE staff
ADD FOREIGN KEY (`personIdentityNo`)
REFERENCES Persons(`personIdentityNo`)

I've also tried it from the customer table with the same error.

If you need any further info to make sense of this, please let me know. Any pointers and hints would be gratefully received.

tincat

After typing the above out and rereading, I realised why the error was coming up; I had persons (personIdentityNo etc) instead of person (personIdentityNo etc) - i.e. just an extra s

So my 2nd question has been answered - thanks.

Can anyone with experience please tell me if it is a good idea to take the common info and put it in a separate table as per my 1st question? Is there anything I should be aware of?

I have set the personIdentityNo to automatically increment as a new person is added, whether it is a staff member or customer.

Lokolo

1) Yes thats good.

2) Not sure. When I did phpmyadmin a long time ago it came with apache stuff for you to view the table in a viewable format, then when I edited anything on the table, it gave me the code it did. Could you do this?

----

edit nevermind you solved it.

Lokolo

FYI the method you've used it for a relational database. It is to stop duplication of information (as you said yourself). Personally I wouldn't bother with the person one. Just have Staff, Customer and Transactions (you have branches ID too, so I imagine you have a branches table?).

---

Branches - Branch ID (PK), Address
Staff - Staff ID (PK), Branch ID (FK), Name, Address, Salary
Customer - Customer ID (PK), Name, Address
Transactions - Transction ID (PK), Staff ID (FK), Customer ID (FK)

---

If this is an assignment and you have to write a report, you'll want to mention that you made your database around relational model and give reasons why.

tincat

Hi Lokolo,

Yes, we had to do an ER class diagram based on a shopping scenario, and then we have to put it into a database and fill it with data, and then do particular SQL queries on it, showing the output.

So do you think I should just have the repeating information (name, address, town, postcode, phone number and email etc etc) in both customers and staff?

If I did choose to have a 'person' table, do you think the method I've used will work?

I'm still struggling slowly finding my way around phpmyadmin, so I haven't got to the stage yet where I can populate all 10 tables with data and run the queries.

Thanks for answering

Lokolo

If you staff and customers were the same, then yes, you should have a person. But they're not going to be (in a normal shop scenario), it's data thats the same (not the same type, but exactly the same) that you shouldn't repeat.

e.g.

Your Method

Person 1 - PID1, Bob Smith, 5 Hobbit Street
Person 2 - PID2, Alan Tool, 18 Manchester Road
Customer 1 - CID1, PID1
Staff 1 - SID1, PID2, £15,000, Assistant

My Method

Customer 1 - CID1, Bob Smith, 5 Hobbit Street
Staff 1 - SID1, Alan Tool, 18 Manchester Road, £15,000

---

So I have cut the number of records in half. And reduced by a whole table. Which saves memory.

However, if you have staff being customers, so if we have one person, Bob Smith, then my method would be s***.

Customer 1 - CID1, Bob Smith, 5 Hobbit Street
Staff 1 - SID1, Bob Smith, 5 Hobbit Street, £15,000

As you can see, the actual data has been duplicated (highlighted in red).

Why is this a problem?

Bob moves house. I change the staff information but not the customer information:

Customer 1 - CID1, Bob Smith, 5 Hobbit Street
Staff 1 - SID1, Bob Smith, 20 Hobbit Street, £15,000

So now when it comes to reading the information to send his ordered items to, they go to his old address because the person updating didn't know he also had a customer record. :eek::eek::eek: