Big picture, whats the best way to make a database driven menu ?

[floridaflatlander]

Whats the best way to make a database driven menu ?

 

Right now I have a db that looks like

id | boats | Inshore

id | boats | Offshore

id | boats | Bay Boats

and so on

 

I'm think of going to

database 1

id-1 | boats

 

database 2

id | 1 | inshore

 

or would a multideminsional array work best.

 

My goal is to have the short term format be

 

Boats

Inshore

Offshore

Bay Boats

 

and in the long term a format whose style I can change later, I wanted to get ideas from people in the know and have done this before.

 

Thanks in advance

[AyKay47]

if you can, always try to store data in the least amount of tables possible.. something like this should be able to be stored in one table.

what is wrong with the way your table is now?

[floridaflatlander]

Thanks for the reply

I really don't have a problem now. It's just that I was reading different books and online articles and was thinking I was wanting some opinions from people that had done this before.

For example one book said don't repeat rows in columns, for example “Boats” in multiple rows like I have now, if you do make another table and another book hinted that arrays were the way to go

 

Again thanks

[AyKay47]

well ultimately the goal is to make mysql do as little work as possible.. the fewer rows in your db  the better.. if you do decide to go the array route.. you will want to use serialize to store the array in the db table, then use unserialize when extracting the array from the db to convert it into array form again for use.

[KevinM1]

if you can, always try to store data in the least amount of tables possible.. something like this should be able to be stored in one table.

what is wrong with the way your table is now?

 

What?  No, not at all.

 

Relational databases are supposed to have as many tables as needed to avoid insert/delete anomalies.  This, in turn, is why tables should be normalized.  Cramming a bunch of info into columns like a spreadsheet is exactly the wrong way to go, and negates the entire point of using a relational database.

 

@floridaflatlander, the other sources you looked at had the right of it.  Repeated values in columns is often a sign that your database design is bad.  Can you show how all of your tables are structured currently?  Getting your database design right at the start will protect you from headaches down the road.

[floridaflatlander]

Basically right now I have

 

category

cat_id | category | item

example

1 | boats | inshore

 

members

mem_id | name | address | pw ... and stuff

 

photos

photo_id | id_item | thumb(address in file) | photo(address)

 

items

id_tem | mem_id | cat_id | title | description | date

 

Thanks for the look see

[KevinM1]

Split your categories from the individual items that belong to them:

 

categories:

cat_id | cat_name

 

items:

item_id | cat_id | name | description | date

 

Then, assuming that members can have multiple items, make the following pivot table -

 

items_members:

item_id | mem_id

 

That table is the magic glue that makes your many-to-many relationship between items and members work.  You don't need a native id column in that table as each row is unique.  Example:

 

item_id | mem_id

1            14

1            25

1            03

1            66

2            14

2            15

3            66

4            15

4            03

4            25

 

In the example above, member 15 owns/is related to items 2 and 4.  Similarly, item 1 is owned by members 14, 25, 3, and 66.

[floridaflatlander]

Thanks everyone

 

I think I'll stay with what I have for the time being, with the exception of the category file there is no data repeated in any off the dbs and all are linked with indexs on auto increment.  I don't see having more than 30-40-50-60 items and if I do I  may or may not split the category db into two.

 

Thanks again