Hiya guys,
I'm trying to come up with a workable database structure / user interface, and I'm really interested to see what you think is the best solution.
Lets say that I have a table storing a list of items, lets say basic components. This table simply stores an ID and a name for each item.
Then, a specific unit it created by selecting these components from a group of tick boxes on a page. What would be the best way to structure the database that holds these units, given that the number of components can vary from just a few to, well, how ever many components there are.
My initial thought was to just give it an ID, and a name, and then a single field with a comma separated list of the components and just save one row per unit. The problem then is display ... how do I then split that list and attach values to the tick boxes, and vice versa?
Alternatley, I add a row per component and tag them with a unit number.
The thing is, I also want to create a page where a unit can be loaded, and then show a list of used components all ticked, and list of all components all unticked, and allow the default components to be changed. Again I was thinking that the easiest way to store this data is in two columns of comma separated values, one components removed, and one components added. Of course, I would also have to store the standard unit profile in there also.
What would be the best db layout for this kind of thing? And how would I go about attaching the values to the tick boxes of the forms.
Any help would be great, thanks.
Database Structure - Best method?
Moderator: General Moderators
-
TheBentinel.com
- Forum Contributor
- Posts: 282
- Joined: Wed Mar 10, 2004 1:52 pm
- Location: Columbus, Ohio
Re: Database Structure - Best method?
It sounds like you have components and you have units made up of one or more components. For example, you might have "screws" and "trailer hitches" and "tires" as components, and your units might be "car" and "truck" and "ship". So your data would sort of be:
car: screws, tires
truck: screws, trailer hitches
ship: screws
Except you don't want to use a comma-sep list since it makes it impossibe to search it efficiently later. You have to read and parse each componet-list field in every record to find what you're looking for.
Instead, use a table that references one unit and one component. Then your data looks more like this:
Unit | Component
car | screws
car | tires
truck| screws
truck| trailer hitches
ship | screws
Then it's easy to find the components of a unit and it's easy to see what units use what components. Both are efficient searches. And it's much easier to delete a component.
Of course, this table would only hold the keys, not the actual data. Instead of "car" it would say "35" or whatever your numeric key for that record is.
Hope it helps.
car: screws, tires
truck: screws, trailer hitches
ship: screws
Except you don't want to use a comma-sep list since it makes it impossibe to search it efficiently later. You have to read and parse each componet-list field in every record to find what you're looking for.
Instead, use a table that references one unit and one component. Then your data looks more like this:
Unit | Component
car | screws
car | tires
truck| screws
truck| trailer hitches
ship | screws
Then it's easy to find the components of a unit and it's easy to see what units use what components. Both are efficient searches. And it's much easier to delete a component.
Of course, this table would only hold the keys, not the actual data. Instead of "car" it would say "35" or whatever your numeric key for that record is.
Hope it helps.
Thanks for the reply, and yeah, thats pretty much exactly what I was after.
I'm wondering though where that leaves me for the customisation table. When a page loads, it shows the original components and then allows the user to remove some of the standard ones, while adding some additional ones. Then when it is re-displayed, it would show the title of the original unit, and then only the components that were removed or added.
Just interested as to how you would handle the database for storing the data needed to do this.
Thanks again.
I'm wondering though where that leaves me for the customisation table. When a page loads, it shows the original components and then allows the user to remove some of the standard ones, while adding some additional ones. Then when it is re-displayed, it would show the title of the original unit, and then only the components that were removed or added.
Just interested as to how you would handle the database for storing the data needed to do this.
Thanks again.
-
TheBentinel.com
- Forum Contributor
- Posts: 282
- Joined: Wed Mar 10, 2004 1:52 pm
- Location: Columbus, Ohio
I'm pretty sure I am misunderstanding you. It sounds like you want to display a "car", for instance, and show the components currently known to be a part of a "car". In addition, you want to show all the other components so the user can add them, "steering wheel" for instance, or "stereo". So your page would look like this:Stryks wrote:I'm wondering though where that leaves me for the customisation table. When a page loads, it shows the original components and then allows the user to remove some of the standard ones, while adding some additional ones. Then when it is re-displayed, it would show the title of the original unit, and then only the components that were removed or added.
CAR
===
components in use
---------------------
[x] screws
[x] tires
components unused
----------------------
[ ] steering wheel
[ ] stereo
<Submit>
The user could then uncheck the "in use" and check the "unused" components until they thought they were right, then click submit.
Your PHP program would delete the components that were in use, but are now unchecked, and add the components that were unused and now checked.
The next time a "car" is displayed, it would show the updated list of components. Whatever script displayed them the first time would be the same script that displays them now. The fact that they were updated inbetween is irrelevant.
Of course, you may want to put time stamps and userid's all over these tables to know when changes were made and by whom. And I don't mean to suggest your interface should actually look like the one I typed above, that's just a quickie way to represent it.
Is that what're you're looking for?
Thats pretty close to what I mean, except. Well, lets say that the page where people choose the components is only for one group of people.
The second page would show things a little differently. I guess thats where the cnfusion about structure comes in.
Page 1 is as you say,
CAR
===
components in use
---------------------
[x] screws
[x] tires
components unused
----------------------
[ ] steering wheel
[ ] stereo
So the user can change the component list away from the default.
On the second page however, the unit 'CAR' would be a standard product, and as such a component list wouldn't be needed, instead just the variation from standard configuration would be needed.
Like,
Unit for User 1:
CAR
Modifications:
- Screws
+ Steering Wheel
I guess that is what is stumping me. How can I hold that data? I am imagining it going through as an order, so it would be saved to a table called orders with a component list, but I am unsure of how to establish that relationship with its original cofiguration.
I hope I made this somewhat clearer. Actually, I think I just confused myself more. Any ideas? Thanks.
The second page would show things a little differently. I guess thats where the cnfusion about structure comes in.
Page 1 is as you say,
CAR
===
components in use
---------------------
[x] screws
[x] tires
components unused
----------------------
[ ] steering wheel
[ ] stereo
So the user can change the component list away from the default.
On the second page however, the unit 'CAR' would be a standard product, and as such a component list wouldn't be needed, instead just the variation from standard configuration would be needed.
Like,
Unit for User 1:
CAR
Modifications:
- Screws
+ Steering Wheel
I guess that is what is stumping me. How can I hold that data? I am imagining it going through as an order, so it would be saved to a table called orders with a component list, but I am unsure of how to establish that relationship with its original cofiguration.
I hope I made this somewhat clearer. Actually, I think I just confused myself more. Any ideas? Thanks.