How to design parking street database?

Question

I try to design database which contains data about street parking. Parking have gps coordinates, time restriction by day, day of week rules (some days are permitted, other restricted), free or paid status. In the end, I need to do some queries that can specify parking by criteria. For first overdraw I try to do something like this:

Pakring
-------
parkingId  
Lat
Long
Days (1234567)
Time -- already here comes trouble

But it`s not normalized and quickly overflow database. How to design data in the best way?

Update For now I have two approaches The first one is:

I try to use restrictions tables with many-to-many links.(This is example for days and months). But queries will be complicated and I don`t now how to link time with day. The second approach is:

Using one restricted table with Type field, that will have priority. But this solution also not normalized. Just to be clear what data I have.

PakingId Coords String Description(NO PARKING11:30AM TO 1PM THURS)

And I want to show user where he can find street parking by area, time and day. Thanks to all for your help and time.

Answer 1

This seems like a difficult task. Just a few thoughts.

Are you only concerned with street parking? Parking houses have multiple floors so GPS coordinates won't work unless you stay on the streets.

What is the accuracy of the coordinates? Would it be easier to identify each parking space individually by some other standard. Like unique identifiers of the painted parking squares. (But what happens if people don't park into squares? Or the GPS coordinates accuraycy fails/is not exact enough because of illegal parking? Do you intend to keep records of the parking tickets too?)

Some thought for the tables or information you need to take into account:

1. time: opening hours, days
2. price: maybe a different price for different time intervals?
3. exceptions: holidays, maintenance (maybe not so important, you could just make parking space status active/inactive)
4. parking slot: id (GPS/random id), status

Three or four tables above could be linked by an intermediate table which reveals the properties of a parking space for every possible parking time (like a prototype for all possible combinations). That information could be linked into another table where you keep records of a actual parking events (so you can for example keep records of people who have or have not paid their bills if you need to).

There are lots of stuff that affect your implementation so you really need to list all the rules of the parking space (and event?). Database structure can be done (and redone) later after you have an understanding of the properties of the events you need to keep records of. And thats the key to everything: understanding what you need to do so you can design and create the implementation. If the implementation (application) doesn't work change the implementation. If the design is faulty redesign. If you don't undestand the whole process (what you really need), everything you do is bound to fail. (Unless you are incredibly lucky but I wouldn't count on luck...)

Answer 2

As you seem to have heard of normalization, and following the comment from Damien, you should use different tables to represent different things.

You should then think about how to link those tables together, and in the process define the type of relationship between the 2. Could be one-to-one (this one is the one where you could be tempted to put everything in the same table, but a simple foreign key in a linked table is cleaner), one-to-many (this is where the trouble would begin if you put everything in one table, cause now there will be several lines in the linked table with the same foreign key, and if everything was in the same table, you'd have to myltiply the fields in that table), or many to many (where you would need to add a table only to make the link between 2 other tables, thus with 2 foreign key fields pointing to records in both tables).

For example, in your case, a Parking table could hold the parking name, coordinates, etc. A second table TimeTable could hold the opening days/time for each parking, with a foreign key to the parkingId (making it a one-to-many rlationship, 1 parking can have many opening frames). The fields of this table could for example be DayOfWeek (number indicating the day), openingTime, closingTime. This would allow you to define several timeframes on the same day, or a single one (if it's always open for example), giving in this case 7 records in this table for this parking (=> one-to-many relationship). You could then imagine a 3rd table Price where you put data concerning the price of that parking (probably a one-to-many too, with records for hourly rates/long stay/..., and so on depending on the needs and the different "objects" you would need to represent.

Please note these are only rough examples. Database design can sometimes be very tricky and that's a matter I'm not specialist in, but I think these advises can help you go further and come back with another question if you get stuck.

Good luck !

Answer 3

Try using two tables with an intersection entity between them.

Table parking will have parking_id, lat and long columns. Table Restrictions will have all the type of restrictions that you have in your scenario with something like restriction_id, restriction_day, restriction_time and restriction_status and maybe restriction_type.

Then you can link the two tables with foreign key constraints in the intersection entity.

Example parking_id has restriction_id.

This way a parking can have more than one restriction and a restriction can be applied to more than one parking.