| SitePoint Sponsor |
Hi Guys,
I am going round in circles trying to sort this out.
I wish to move my design to the next step. I am using MYSQL for my database.
I am not sure if i am to include the fields that are duplicated in other tables (foreign keys) or do i add them at a later data?
Can anyone help put me in the right direction so i can complete the schema including relationships, foreign keys etc.
PLEASE SEE ATTATCHED MODEL
Many Thanks
Richard Howells
my email
You don't have to include the fields from other tables - the foreign keys provide the link to that, subsequent JOIN queries can fetch data from associated tables without the need for duplicated fields.
Okay,
I think I know what you mean.
So for my schema, should it just include the 'create table and create the fields'?
How do i tell sql that there are foreign keys and relationships within the tables?
Or am i missing the point?
Rich
You don't have to tell MySQL that, just make sure that you use the same datatypes for the keys across all tables (which isn't MySQL specific mind you). MySQL doesn't have a 'relationships' window like Access in which you define the stuff. Just design the tables and your scripts do the rest.
you must also be clear on the purpose of a model
the model is intended to show the logical structure of the data
in that regard, it has primary and foreign keys
now, when you go to implement your model, you translate it into a physical design, which, if you are using mysql myisam tables, does not support foreign keys
so, a recap -- the model includes foreign keys, even if mysql doesn't support them
now, at the risk of confusing you further, you should nevertheless define the foreign keys in your CREATE TABLE statements anyway!
even though myisam tables don't support them, the syntax is checked and therefore it acts, at the very least, as documentation
p.s. and you still have an extra table between customers and products
Hi thanks,
that is there because otherwise there would be a many to many relationships, because of this an extra entity is needed.
Or am i wrong here ?
richard
PS this tables stores or should I say tracks the products that a customer views.
richard
customer_product_inspection primary key is the composite of customer_id, product_id, and datetime_inspected (which is far better than separate date and time attributes)
remove tables customer_interests and interests
remove FK interests_id from products
add new customer_interests table with customer_id FK, product_id FK, and primary key the composite of the two
ill edit the model asap and post it on here for you to have a look
i appreciate your help
cheers
richard
Okay I decided to take out the customer_id and use the login_name as primary key for the customers because no to login in names can be the same
also customers interests relates to the product types i.e memory, graphics cards, processors etc.
hope this is okay for you
thanks
richard
Just a thought !
Isnt there now a many to many relationship between:
customer_product_interest AND products tables ?
Richard
no, the relationship from customer_product_interest to product is many-to-one
by the way, drop column customer_product_interst, and make the PK the combination of login_name and product_id (not product_type)
do not introduce surrogate keys into a logical model
you may do so in the physical model, but only if you have a good reason, but for this design, you don't have one
ditto customer_product_inspect, ditch that and make the PK the combination of the other three columns
and remove interest_id from product
Im not sure what you mean here:
Or here:by the way, drop column customer_product_interst, and make the PK the combination of login_name and product_id (not product_type)
Ive attatched my model in word formditto customer_product_inspect, ditch that and make the PK the combination of the other three columns
and remove interest_id from product
Cheers
Richard
word format is way worse than the images you posted earlier, so i didn't look at it
you do not understand what i mean by "drop column customer_product_interst" ??? remove it from the model and diagram
"make the PK the combination of login_name and product_id (not product_type)" assumes the relationship is from customer to product, not product type
you didn't have a product type entity
if you want a relationship between customer and product type, you have to have a product type entity
i think you are getting frustrated by this process because you don't understand the basic method of modelling a relationship, and every suggestion you get from us is still confusing, and you are making revision after revision but you still don't understand what's going on
just an observation
Im sorry you are right It is confusing when im getting told to make changes etc to the model. But Im learning as Im going
Ive added in the extra table called product_types because the customers interests are for product types e.g memory, graphics cards etc.
These interests are entered when the user registers on the website. THen once they are logged in random products are displayed relating to their interests.
Ive made the changes to the model please see earlier thread witrhin this discussion.
I now am wondering if there is a problem between the relationships of the bottom three tables
Cheers
Richard
Hey, I already deleted one of the tables too many, and you put it back in!
the latest model looks fine except for one thing -- you must still remove column customer_product_inspect
Okay ive up loaded the model again you can still view it in the disscusion.
What do you reckon now to it ?
Does it work for you?
Hopefully its complete !!
Cheers Richard
i still see customer_product_inspect, and i've reloaded the diagram
Originally Posted by asterix
yes appreciate your help asterix, but it seems the model has developed and changed slighlty now, what do you reckon to it?
best regards
richard
whoops, wait a sec, there we go, it's gone now
however, the PK of customer_product_inspection should be all 3 columns, not just the first two, otherwise a customer will only be inspection-linked to a given product at most once (is that what you want?)
There we go, done I hope.
What do you think now.?
however, the PK of customer_product_inspection should be all 3 columns, not just the first two, otherwise a customer will only be inspection-linked to a given product at most once (is that what you want?)
It would make sense to have all three as PK.
Many Thanks
Richard
yeah, looks great, i hope we, er, i mean, i hope you get an A
many thanks for the help ill let you know how things go
cheers again
richard
Posting Permissions
Bookmarks